I need help with a query!
Currently using .extra to do full text query on the article.title field.
art_list = Article.objects.filter(status__status='PUBLISHED')
art_list = art_list.extra(
where=['title_tsv @@ plainto_tsquery(%s)'],
That works great.
I want to add the ability to search in a table of tags, so that I return
articles with matching titles plus articles with tags that match the
search term. That involves a OR statement, for which I believe I need
to use a Q object.
What I can't figure out is how to use the Q object with the .extra.
Additionally, I need to select distinct() because there can be more than
one matching tag.
The end SQL should look something like this:
SELECT DISTINCT article.*
FROM article JOIN article_tags ON article.id = article_tags.article_id
JOIN tag ON article_tags.tag_id = tag.id
JOIN article_status ON article_status.id = article.status_id
WHERE article_status.status = 'PUBLISHED' AND (tag.name LIKE '%volcano%'
OR title_tsv @@ plainto_tsquery('Aarhus')) ORDER BY article.title;
That gives the correct results, although it's a bit slow.
What I want might look something like this:
art_list = Article.objects.filter(status__status='PUBLISHED',
QExtra(where=['title_tsv @@ plainto_tsquery(%s)'],
I may end up doing the tag query using full text search, in which case
it would have to go in the extra(), but I'm not sure about the syntax
for doing the joins "manually" in extra().
Anyone have any suggestions on how to do this?