当前位置: 动力学知识库 > 问答 > 编程问答 >

postgresql full text search query to django ORM

问题描述:

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

网友答案:

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;""")
分享给朋友:
您可能感兴趣的文章:
随机阅读: