postgresql full text search query to django ORM

Question

I was following the documentation on FullTextSearch in postgresql. I've created a tsvector column and added the information i needed, and finally i've created an index. Now, to do the search i have to execute a query like this

SELECT *, ts_rank_cd(textsearchable_index_col, query) AS rank
FROM client, plainto_tsquery('famille age') query
WHERE textsearchable_index_col @@ query
ORDER BY rank DESC LIMIT 10;

I want to be able to execute this with Django's ORM so i could get the objects. (A little question here: do i need to add the tsvector column to my model?) My guess is that i should use extra() to change the "where" and "tables" in the queryset

Maybe if i change the query to this, it would be easier:

SELECT * FROM client
WHERE plainto_tsquery('famille age') @@ textsearchable_index_col
ORDER BY ts_rank_cd(textsearchable_index_col, plainto_tsquery(text_search)) DESC LIMIT 10

so id' have to do something like:

Client.objects.???.extra(where=[???])

Thxs for your help :) Another thing, i'm using Django 1.1

Steve Jalim · Accepted Answer · 2010-08-13 08:26:56Z

5

Caveat: I'm writing this on a wobbly train, with a headcold, but this should do the trick:

where_statement = """plainto_tsquery('%s') @@ textsearchable_index_col 
                     ORDER BY ts_rank_cd(textsearchable_index_col, 
                                         plainto_tsquery(%s)) 
                     DESC LIMIT 10"""

qs = Client.objects.extra(where=[where_statement], 
                          params=['famille age', 'famille age'])

If you were on Django 1.2 you could just call:

Client.objects.raw("""
SELECT *, ts_rank_cd(textsearchable_index_col, query) AS rank
FROM client, plainto_tsquery('famille age') query
WHERE textsearchable_index_col @@ query
ORDER BY rank DESC LIMIT 10;""")

answered Aug 13, 2010 at 8:26

Steve Jalim

12.2k1 gold badge39 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pleasedontbelong Over a year ago

i've tried it but it throws an error saying that there's a problem on the syntax of the query which is weird because i took the qs.query and executed directly in the database, and it works fine, i guess the problem is in the single quotes in the "plainto_tsquery('%s')"

Collectives™ on Stack Overflow

postgresql full text search query to django ORM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related