On Thu, 2009-03-05 at 14:19 +1100, Malcolm Tredinnick wrote:
[...]
> Second version: I want books that are in all three categories, but just
> those three categories and *no others*.
>
> Answer: hmmm... :-(
>
> This one is possible in raw SQL, but it's a fairly fiddly query. I can't
> make it work with annotate() yet, although I just played around for
> about 30 minutes to see what was going on. It might be a subtle
> limitation in annotate(), but that's only a new feature, so we can
> extend it in the future. Basically, the count I do in the first answer
> counts the number of categories that have been selected, not the total
> number of categories available for each book (which would require some
> extra table joins). I'd also want to filter on the total number of
> categories available in this version of the question, so I get stuck.
So, naturally, as soon as I hit "send", the solution occurs to me. Not
short, but it works. Here's how to get all books that are in precisely
those three categories: no more and no less (line breaks inserted for
readability, hopefully):
Book.objects.
filter(categories__in=[1, 2, 3]).
annotate(num_cats=Count('categories').
filter(num_cats=3).
exclude(id__in=
Book.objects.annotate(all_cats=Count('categories')).
filter(all_cats__gt=3))
It's a pretty ugly SQL query and I haven't put in the time to work out
if it's the most efficient we can do. But it does the right thing.
Again, requires Django 1.1-alpha because it relies on annotations and
nested querysets.
Regards,
Malcolm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@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
-~----------~----~----~----~------~----~------~--~---
