## 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 class Category(models.Model):
2 related = models.ManyToManyField('self',
3 null = True, blank = True,
4 symmetrical = False,
5 through = 'CategoryRelation')
```

Assuming the following intermediate `through = ...`

relation:

```
1 class CategoryRelation(models.Model):
2
3 source = models.ForeignKey('Category'
4 null = False, blank = False,
5 related_name = 'relation_source')
6
7 target = models.ForeignKey('Category',
8 null = False, blank = False,
9 related_name = 'relation_target')
10
11 order = models.PositiveIntegerField('order',
12 default = 0, null = False, blank = True)
13
14 class Meta:
15 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 relations = CategoryRelation.objects.filter(
2 source = self)
3 Category.objects.filter(
4 relation_target__in = relations).order_by(
5 'related')
```

The following works for ordering correctly, but does not leave out duplicate entries:

```
1 relations = CategoryRelation.objects.filter(
2 source = self)
3 Category.objects.filter(
4 relation_target__in = relations).order_by(
5 '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 return Category.objects.filter(
2 relation_target__in = relations).annotate(
3 relation_target_order = models.Min(
4 'relation_target__order')).order_by(
5 '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 return Category.objects.filter(
2 relation_target__in = relations).annotate(
3 relation_target_order = models.Min(
4 'relation_target__order')).order_by(
5 '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, \ ...`

## Comments

comments powered by Disqus