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) 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

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

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.

{% for item in order.order_items.all %}
  {{ item }}
{% endfor %} 

also yields 4 items instead of 1 due to the left join.