Hi --
I've been programming with Django about a year, and really like it, but
I've run into something that I can't quite solve on my own.
I think someone else may be able to offer the answer, or at least a hint
to send me in the right direction.
I have two tables I want to search in simultaneously, Article and Tag.
They have a many-to-many relationship.
I want to get a QuerySet of Articles that match a query term on
article.title, as well as those that match on tag.name.
I can get what I want using SQL, but how do I do it in Django (with
acceptable performance)?
Ideally, I want to be able to add .filter() and .exclude() to the
QuerySet. These SQL examples below are the basic idea, but in some
cases I'm adding .filter and .exclude.
Currently, the code looks something like this (I simplified it a bit,
but this should give an idea):
>
> art_list = Article.objects.filter(status__status='PUBLISHED')
> art_list = art_list.extra(
> where=["title_tsv @@ plainto_tsquery(%s)"],
> params=[term])
> if channel:
> art_list = art_list.filter(channel=channel)
> art_list = art_list.distinct()
> art_list = art_list.order_by(*order_by)
Somehow, I want to include the fulltext search on the tag.name field,
where=['name_tsv @@ plainto_tsquery(%s)'],
params=[term])
The two SQL example queries below both give the correct results.
However, the first one with joins takes over 20 seconds.
The second one with UNION is quite fast.
I think what I need to do might be to create a custom manager for
Article that would execute the UNION query and return a QuerySet with
all the matching Articles. I'm not sure quite how to do that,
especially if I expect it to work with the .filter() and .exclude()
methods.
-- full-text search on article.title and tag.name
SELECT DISTINCT article.title
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 @@ plainto_tsquery('french restaurants')
OR title_tsv @@ plainto_tsquery('french restaurants'))
ORDER BY article.title;
-- alternative full-text search on article.title and tag.name with UNION
-- with ranking
SELECT DISTINCT article.title, ts_rank_cd(title_tsv, q1) AS rank
FROM plainto_tsquery ('french restaurants') AS q1, article
JOIN article_status ON article.status_id = article_status.id
WHERE article_status.status = 'PUBLISHED' AND
title_tsv @@ q1
UNION
SELECT DISTINCT article.title, ts_rank_cd(name_tsv, q2) AS rank
FROM plainto_tsquery('french restaurants') AS q2, article
JOIN article_tags ON article.id = article_tags.article_id
JOIN article_status ON article.status_id = article_status.id
JOIN tag ON article_tags.tag_id = tag.id
WHERE article_status.status = 'PUBLISHED' AND
tag.name @@ q2
ORDER BY rank DESC, title;
So -- if anyone can offer any advice on this, I would very much
appreciate it!!!
Best,
Liam
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
