## 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.*

Assuming the following **Model**:

1
2
3
4
5

class Category(models.Model):
related = models.ManyToManyField('self',
null = True, blank = True,
symmetrical = False,
through = 'CategoryRelation')

Assuming the following intermediate `through = ...`

relation:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

class CategoryRelation(models.Model):
source = models.ForeignKey('Category'
null = False, blank = False,
related_name = 'relation_source')
target = models.ForeignKey('Category',
null = False, blank = False,
related_name = 'relation_target')
order = models.PositiveIntegerField('order',
default = 0, null = False, blank = True)
class Meta:
ordering = ( 'order', )

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

:

1
2
3
4
5

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:

1
2
3
4
5

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:

1
2
3
4
5

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:

1
2
3
4
5

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:

1

assert self.distinct == rhs.distinct, \ ...