Dr Alexis Petrounias

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:

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

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, \ ...