Ordering on a field in the 'through' Model of recursive ManyToMany relation in Django.

Question

Assuming the following model:

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

Assuming the following intermediate 'through' relation:

class CategoryRelation(models.Model):
    source = models.ForeignKey('Category', null = False, blank = False, verbose_name = _('source'), related_name = 'relation_source')

    target = models.ForeignKey('Category', null = False, blank = False, verbose_name = _('target'), related_name = 'relation_target')

    order = models.PositiveIntegerField(_('order'), null = False, blank = True, default = 0)

    class Meta:
        ordering = ( 'order', )

How can I obtain the Category objects related to a given Category while preserving the ordering? The following code will produce the correct Category objects, not in the correct order, and include duplicate entries in the Query Set even when using .distinct():

relations = CategoryRelation.objects.filter(source = self)

Category.objects.filter(relation_target__in = relations).order_by('related')

Thanks!

Answer 1

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 the .order_by() logic is applied afterwards. 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 combination 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, \ ...