#35751: Ordering a model via a m2m field creates unintended side effect for
ForeignKeys
-------------------------------------+-------------------------------------
Reporter: capital-G | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
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
-------------------------------------+-------------------------------------
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[11]: '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>
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/01070191dd3447cb-c8042a95-ed3a-4308-8cd6-a6a2c83e694b-000000%40eu-central-1.amazonses.com.
