Ordering on a field in the "through" Model of a recursive ManyToMany relation in Django
It is not possible to order a Django Model on a field of the Model acting as the intermediate through = ...
of a ManyToMany
relation, because queries will not return items in the correct order, and in addition will include duplicate items, even when using .distinct()
.
This problem is current as of Django 1.2 and has no generic solution as far as I know.
Problem
Assuming the following Model:


Assuming the following intermediate through = ...
relation:


How can we obtain the Category
objects related to a given Category
while preserving their ordering? The following code will produce the correct Category
objects, not in the correct order, and include duplicate entries in the QuerySet
even when using .distinct()
:
relations = CategoryRelation.objects.filter(source = self)
Category.objects.filter(
relation_target__in = relations).order_by('related')
The following works for ordering correctly, but does not leave out duplicate entries:
relations = CategoryRelation.objects.filter(
source = self)
Category.objects.filter(
relation_target__in = relations).order_by(
'relation_target')
Calling .distinct()
will not make a difference, because .order_by(...)
is applied by the ORM after the SQL SELECT DISTINCT
clause is built. However, it is possible  in this case  to exploit the fact that the order is a positive integer field, and annotate each Category
with the Min value of the order field of the relation_target
field, and use this new annotation field for ordering:
return Category.objects.filter(
relation_target__in = relations).annotate(
relation_target_order = models.Min(
'relation_target__order')).order_by(
'relation_target_order')
This is almost complete, but since the semantics of this query essentially make it unique, it would be wise to call .distinct()
just to make sure the distinct flag is True
so that later combinations with other distinct queries can take place:
return Category.objects.filter(
relation_target__in = relations).annotate(
relation_target_order = models.Min(
'relation_target__order')).order_by(
'relation_target_order').distinct()
In this case .distinct()
does not affect the query in the slightest, but ensures that db/models/sql/query.py
method combine (self, rhs, connector)
passes its assertion:
assert self.distinct == rhs.distinct, \ ...