It's a subtle difference between how a single filter works and two filters work together over to-many relationships. Here's a writeup that I found helpful: https://blog.ionelmc.ro/2014/05/10/django-sticky-queryset-filters/
On Thu, Mar 29, 2018 at 4:26 PM, Andrew Standley <airstand...@gmail.com> wrote: > I have recently become acquainted with some ORM behaviour for reverse > relationships that "makes no sense", and I'm hoping someone can explain the > justification for the current behaviour. > > This specifically relates to `filter` behaviour referenced in 29271 > <https://code.djangoproject.com/ticket/29271>, and 16554 > <https://code.djangoproject.com/ticket/16554> which seems tangentially > related to several issues with `exclude` (24421 > <https://code.djangoproject.com/ticket/24421>, 14645 > <https://code.djangoproject.com/ticket/14645>, 17315 > <https://code.djangoproject.com/ticket/17315>) and aggregate expressions ( > 16603 <https://code.djangoproject.com/ticket/16603>, 19415 > <https://code.djangoproject.com/ticket/19415>) > > Most of the confusion about 'intended' behaviour and confirmed 'bugged' > behaviour seems to relate to the ORM's use of joins for reverse > relationships. > I think my personal confusion boils down to two questions. > > 1) Is there some fundamental limitation in the ORM that prevents reducing > the number of joins? Several of these tickets indicate how the ORM could > potentially produce similar results with queries that did not use multiple > joins. Why is that not desirable behaviour? > > 2) Why is the current behaviour of `filter` for multi-value relationships > 'intended'? I'm hoping I am missing something obvious but it seems to me > that `Q` objects would support the type of behaviour suggested in the spanning > multi-valued relationships > <https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships> > documentation > in a much more inituative manner. In a test case with models > > class Related(models.Model): > field = models.CharField(max_length=100) > > class Main(models.Model): > field_one = models.CharField(max_length=100) > field_two = models.CharField(max_length=100) > related = models.ForeignKey(Related, on_delete=models.CASCADE) > > > both > > >>> Related.objects.filter(Q(main__field_two='2')|Q(main__field_one='1')) > > SQL: > SELECT "test_app_related"."id", "test_app_related"."field" FROM > "test_app_related" INNER JOIN "test_app_main" ON ("test_app_related"."id"= > "test_app_main"."related_id") WHERE ("test_app_main"."field_two" = "2" > OR "test_app_main"."field_one" = "1") > > and > > >>> Related.objects.filter(main__field_two='2').filter(main__field_one='1' > ) > > SQL: > SELECT "test_app_related"."id", "test_app_related"."field" FROM "test_app_ > related" INNER JOIN "test_app_main" ON ("test_app_related"."id"= "test_ > app_main"."related_id") INNER JOIN "test_app_main" T3 ON ("test_app_ > related"."id" = T3."related_id") WHERE ("test_app_main"."field_two" = > "two" AND T3."field_one" = "one") > > Produce exactly the same results but the second seems to have an > unnecessary extra join, and directly contradicts the behaviour of filter > with non multi-valued fields. > > > > In short, could someone be kind enough to explain the justification for > all this weird behaviour with multi-value relationships? > > > Cheers, > Andrew > > -- > You received this message because you are subscribed to the Google Groups > "Django developers (Contributions to Django itself)" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to django-developers+unsubscr...@googlegroups.com. > To post to this group, send email to django-developers@googlegroups.com. > Visit this group at https://groups.google.com/group/django-developers. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/django-developers/22234c13-3152-473f-86bd- > 04b41efd9203%40googlegroups.com > <https://groups.google.com/d/msgid/django-developers/22234c13-3152-473f-86bd-04b41efd9203%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CABpHFHQBTdZ%3DVSeDa1APVZUTpoT7cFhgRFxxVgY4cVtnS4GYvg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
