Need help with Query: Q Objects and .extra()

[Info Cascade]

Hi --

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)'],
params=[term])

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)'],
params=[term])) |
Q(tags__name__icontains=term)).distinct()

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?

Liam

[Tomasz Zieliński]

On 13 Lis, 20:58, Info Cascade <informationcasc...@gmail.com> wrote:
>  art_list = Article.objects.filter(status__status='PUBLISHED',
>                 QExtra(where=['title_tsv @@ plainto_tsquery(%s)'],
>                     params=[term])) |
> Q(tags__name__icontains=term)).distinct()

I'm not aware of any QExtra-like functionality (someone correct me
please if I'm wrong here),
but you can do two searches and manually merge the results in Python,
i.e.:

art_list_published = Article.objects.filter
(status__status='PUBLISHED')
art_list_results1 = art_list_published.extra(
where=['title_tsv @@ plainto_tsquery(%s)'], params=[term]))
art_list_results2 = art_list_published.filter
(tags__name__icontains=term)
art_list_results_merged = set(art_list_results1+art_list_results2)

- I'm not sure if set will work here (IMO it should), but it's trivial
to replace it with custom merge


--
Tomasz Zieliński
http://pyconsultant.eu

[est]

Sorry for bumping this old this, but I found solution for this problem

qs = models.MyModel.objects.filter(a=1)
qs |= models.MyModel.objects.filte(b=2).extra(where=[""])

I checke the generated SQL and it's working exactly as expected.

Hope this helps anyone with the same problem.