Probably I should not post into a ten-year old thread, but it was this thread which helped me in my problem, so I want to share my solution to the problem in case anyone else finds this.
Am Samstag, 7. März 2009 06:00:55 UTC+1 schrieb Malcolm Tredinnick: > > One day I'll stop posting in this thread. Really. > > On Sat, 2009-03-07 at 12:03 +1100, Malcolm Tredinnick wrote: > > > However, find things that are simultaneously in all those categories can > > be done without all the annotation nonsense I posted. Simply > > > > > Publication.objects.filter(article=1).filter(article=2).filter(article=3) > I tried that solution, and it works, but as you said is slow. One thing to note is that it includes tables with at least those three articles, so another query is needed to exclude the remaining ones (if an exact matching is what we are looking for). > > > It's a short loop to build up such a set dynamically. The difference > > between that (3 filter calls) and you Q-object version is described in > > the documentation: > > > http://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships > > I forgot to mention that this approach is probably only practical for a > small number of article instances. Each filter() adds a new table join, > so at some point the database will complain. Either literally, about too > many joins, or by taking ages to attempt to optimise and execute the > query. > Exactly, in my tests I found a large difference even with only about 20 joins and 300 objects in the database. Definitely nothing for performance. > For a large number of article instances, the annotation-based solution I > gave earlier is more appropriate. > It does not filter exactly however: If one of the articles is contained, and the num_cats is coincidentially exactly 3, it will include the article. But I wrote, based on your annotation suggestion, a working solution for exact matching of a many-to-many field: def many_to_many_exact(manager, fieldname, foreign_keys, foreign_manager): from django.db.models import Count result = manager filter_dict = {fieldname + '__in': foreign_keys} result = result.filter(**filter_dict) count_name = 'count_' + fieldname annotate_dict = {count_name: Count(fieldname)} filter_dict = {count_name: len(foreign_keys)} result = result.annotate(**annotate_dict).filter(**filter_dict) exclude_dict = {fieldname + '__in': foreign_manager.exclude(pk__in=foreign_keys)} return result.exclude(**exclude_dict) As for the example, this would be the same as the following code: Book.objects .filter(categories__in=[1, 2, 3]) .annotate(num_cats=Count('categories')) .filter(num_cats=3) .exclude(categories__in=Categories.objects.exclude(pk__in=[1, 2, 3])) All of the above code, including the categories for exclude, are executed in one query. Probably the categories of a book are way less than the number of existing categories, thus the database system will be smart and check them against an index in the exclusion part. The resulting query has the following structure: SELECT table.*, COUNT(many_to_many_table.related_id) AS "count_related" FROM table INNER JOIN many_to_many_table ON (table.id = many_to_many_table.table_id) WHERE (many_to_many_table.related_id IN ( ...(e.g. 1,2,3) ) AND NOT (table.id IN (SELECT V1.table_id FROM many_to_many_table V1 WHERE V1.related_id IN (SELECT U0.id FROM related U0 WHERE NOT (U0.id IN ( ...(same as above) )))))) GROUP BY table.id HAVING COUNT(many_to_many_table.related_id) = ...(e.g. 3) Of course there are two nestings, but I am afraid it is not to be solved more efficiently without custom sql. Best regards, Tassilo > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/398bcfa2-9d26-4064-8694-55a65410d0c4%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
