Re: Django ORM Handling of Reverse Relationships and Multi-Value Relationships

So sorry everyone. I meant to post this to the development mailing list. Apparently I haven't had enough coffee today.

Please disregard.

On Thursday, March 29, 2018 at 2:15:33 PM UTC-7, Andrew Standley 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, and 16554 which seems tangentially related to several issues with `exclude` (24421, 14645, 17315) and aggregate expressions (16603, 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 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 what is 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 users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
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/04c5d064-3a2a-4340-928d-c8c3e2f2410c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.