Django: prefetch_related results ordered by a field of an intermediary table

Question

How can I prefetch_related objects in Django and order them by a field in an intermediary table?

Here's the models I'm working with:

class Node(models.Model):
    name = models.CharField(max_length=255)
    edges = models.ManyToManyField('self', through='Edge', symmetrical=False)


class Edge(models.Model):
    from_node = models.ForeignKey(Node, related_name='from_node')
    to_node = models.ForeignKey(Node, related_name='to_node')

    weight = models.FloatField(default=0)

Given a node, I'd like to prefetch all of the related nodes, ordered by weight.

When I use this query:

n = Node.objects.prefetch_related('to_node').order_by('edge__weight').get(name='x')

the order_by has no effect.

Edit:

My best answer so far

n = Node.objects.get(name='x')
edges = Edge.objects.filter(from_node=n).prefetch_related('to_node').order_by('weight')

Then instead of iterating n.edges (as I'd prefer), I iterate edges.to_node

Answer 1

Nowadays, you can also use the Prefetch class to achieve this:

https://docs.djangoproject.com/en/1.10/ref/models/querysets/#django.db.models.Prefetch

Or, if you want to do this all the time as a default, you can look into the meta ordering on the intermediary table, something like:

class SomeThroughModel(models.Model):
    order = models.IntegerField("Order", default=0, blank=False, null=False)
    ...

    class Meta:
        ordering = ['order']  # order is the field holding the order

Answer 2

Just a conceptual idea (written from memory).

The problem is, that the order_by refers to the Node model.

However, there is a way to

Node.objects.get(name='x').edges.extra(select={'weight':'%s.weight' % Edge._meta.db_table}).order_by('weight')

This will force the ORM to:

1. Add 'weight' field, which would normally be omitted.
2. Order the results by it.

Number of queries should be the same as if the prefetch_query worked, one to get the Node, second to get the related nodes.

Unfortunately this is not a very 'clean' solution, as we need to use _meta.