I'm not sure a Manager is what you want, what about Q(http://
docs.djangoproject.com/en/dev/topics/db/queries/#complex-lookups-with-
q-objects) objects? I've used them before and I think it's about what
you'd need. As for performance, not sure how they stack up
On Nov 30, 2:45 pm, Info Cascade <informationcasc...@gmail.com> wrote:
> 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.
