id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35751	Ordering a model via a m2m field creates unintended side effect for ForeignKeys	Dennis Scheiba		"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."	Bug	new	Database layer (models, ORM)	5.1	Normal		ORM ordering	Dennis Scheiba	Unreviewed	0	0	0	0	0	0