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

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.