#35751: Ordering a model via a m2m field creates unintended side effect for
ForeignKeys
-------------------------------------+-------------------------------------
Reporter: capital-G | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ORM ordering | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by capital-G:
Old description:
> Python 3.10 @ Django 5.1.1
>
> Using a many to many relation for ordering (which is something you
> shouldn't do?) will affect the traversing when the object in question is
> accessed via a foreign key - the necessary left join for the ordering
> "spills" into the ORM results, yielding n (number of foreign key
> references) times m (number of many-to-many relations within the object)
> times n objects instead of just the actual n objects.
>
> If you comment out the ordering the ORM behaves as expected.
>
> Th ORM results should not "multiply" due to an ordering configuration.
>
> == How to reproduce
>
> Given a toy `models.py` on a new project which looks like
>
> {{{#!python
> from django.db import models
>
> class Order(models.Model):
> pass
>
> class BookingTime(models.Model):
> date = models.DateTimeField(auto_now=True)
>
> class OrderItem(models.Model):
> order = models.ForeignKey(
> Order,
> on_delete=models.CASCADE,
> related_name="order_items",
> )
>
> booking_times = models.ManyToManyField(
> "BookingTime",
> related_name="order_items",
> )
>
> class Meta:
> ordering = [
> # this is the problem!
> 'booking_times__date',
> ]
>
> }}}
>
> Then on a shell do
>
> {{{#!python
> In [1]: from foo.models import *
>
> In [2]: booking_times = [BookingTime() for _ in range(4)]
>
> In [3]: [b.save() for b in booking_times]
> Out[3]: [None, None, None, None]
>
> In [4]: order = Order()
>
> In [5]: order.save()
>
> In [6]: order_item = OrderItem(order=order)
>
> In [7]: order_item.save()
>
> In [8]: order_item.booking_times.add(*booking_times)
>
> In [9]: order.order_items.count()
> Out[9]: 1
>
> In [10]: order.order_items.all()
> Out[10]: <QuerySet [<OrderItem: OrderItem object (1)>, <OrderItem:
> OrderItem object (1)>, <OrderItem: OrderItem object (1)>, <OrderItem:
> OrderItem object (1)>]>
>
> In [11]: print(order.order_items.all().query)
> SELECT "foo_orderitem"."id", "foo_orderitem"."order_id" FROM
> "foo_orderitem" LEFT OUTER JOIN "foo_orderitem_booking_times" ON
> ("foo_orderitem"."id" = "foo_orderitem_booking_times"."orderitem_id")
> LEFT OUTER JOIN "foo_bookingtime" ON
> ("foo_orderitem_booking_times"."bookingtime_id" = "foo_bookingtime"."id")
> WHERE "foo_orderitem"."order_id" = 2 ORDER BY "foo_bookingtime"."date"
> ASC
>
> In [12]: order.order_items.all().explain()
> Out[12]: '5 0 0 SEARCH foo_orderitem USING COVERING INDEX
> foo_orderitem_order_id_e19c2dbd (order_id=?)\n11 0 0 SEARCH
> foo_orderitem_booking_times USING COVERING INDEX
> foo_orderitem_booking_times_orderitem_id_bookingtime_id_49667055_uniq
> (orderitem_id=?) LEFT-JOIN\n17 0 0 SEARCH foo_bookingtime USING INTEGER
> PRIMARY KEY (rowid=?) LEFT-JOIN\n35 0 0 USE TEMP B-TREE FOR ORDER BY'
> }}}
>
> This also happens in a template, e.g.
>
> {{{#!python
> {% for item in order.order_items.all %}
> {{ item }}
> {% endfor %}
> }}}
>
> also yields 4 items instead of 1 due to the left join.
New description:
Python 3.10 @ Django 5.1.1
Using a many to many relation for ordering (which is something you
shouldn't do?) will affect the traversing when the object in question is
accessed via a foreign key - the necessary left join for the ordering
"spills" into the ORM results, yielding n (number of foreign key
references) times m (number of many-to-many relations within the object)
objects instead of just the actual n objects referencing the foreign key.
If you comment out the ordering the ORM behaves as expected.
Th ORM results should not "multiply" due to an ordering configuration.
== How to reproduce
Given a toy `models.py` on a new project which looks like
{{{#!python
from django.db import models
class Order(models.Model):
pass
class BookingTime(models.Model):
date = models.DateTimeField(auto_now=True)
class OrderItem(models.Model):
order = models.ForeignKey(
Order,
on_delete=models.CASCADE,
related_name="order_items",
)
booking_times = models.ManyToManyField(
"BookingTime",
related_name="order_items",
)
class Meta:
ordering = [
# this is the problem!
'booking_times__date',
]
}}}
Then on a shell do
{{{#!python
In [1]: from foo.models import *
In [2]: booking_times = [BookingTime() for _ in range(4)]
In [3]: [b.save() for b in booking_times]
Out[3]: [None, None, None, None]
In [4]: order = Order()
In [5]: order.save()
In [6]: order_item = OrderItem(order=order)
In [7]: order_item.save()
In [8]: order_item.booking_times.add(*booking_times)
In [9]: order.order_items.count()
Out[9]: 1
In [10]: order.order_items.all()
Out[10]: <QuerySet [<OrderItem: OrderItem object (1)>, <OrderItem:
OrderItem object (1)>, <OrderItem: OrderItem object (1)>, <OrderItem:
OrderItem object (1)>]>
In [11]: print(order.order_items.all().query)
SELECT "foo_orderitem"."id", "foo_orderitem"."order_id" FROM
"foo_orderitem" LEFT OUTER JOIN "foo_orderitem_booking_times" ON
("foo_orderitem"."id" = "foo_orderitem_booking_times"."orderitem_id") LEFT
OUTER JOIN "foo_bookingtime" ON
("foo_orderitem_booking_times"."bookingtime_id" = "foo_bookingtime"."id")
WHERE "foo_orderitem"."order_id" = 2 ORDER BY "foo_bookingtime"."date" ASC
In [12]: order.order_items.all().explain()
Out[12]: '5 0 0 SEARCH foo_orderitem USING COVERING INDEX
foo_orderitem_order_id_e19c2dbd (order_id=?)\n11 0 0 SEARCH
foo_orderitem_booking_times USING COVERING INDEX
foo_orderitem_booking_times_orderitem_id_bookingtime_id_49667055_uniq
(orderitem_id=?) LEFT-JOIN\n17 0 0 SEARCH foo_bookingtime USING INTEGER
PRIMARY KEY (rowid=?) LEFT-JOIN\n35 0 0 USE TEMP B-TREE FOR ORDER BY'
}}}
This also happens in a template, e.g.
{{{#!python
{% for item in order.order_items.all %}
{{ item }}
{% endfor %}
}}}
also yields 4 items instead of 1 due to the left join.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/35751#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070191dd394868-e09c94d9-be8b-4a03-afff-35b6450f5f1d-000000%40eu-central-1.amazonses.com.
