John, Thanks for the response. I don't think Q objects will work, since I need to do the fullsearch, and I'm pretty sure that has to go in .extra(). Also, I think Django would create a query with the joins, which are much slower than the UNION query. Somebody must have encountered this situation before!
Liam John M wrote: > 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 <[email protected]> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > > > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
