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

You are placing the order by clause on the Node Table not on the Edge Table. So, the mentioned result is expected. — Raunak Agarwal, Feb 15 '13 at 23:19

score 10 · Answer 1 · answered Mar 30 '17 at 18:44

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

score 5 · Accepted Answer · answered Feb 16 '13 at 16:16

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:

Add 'weight' field, which would normally be omitted.
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.

score 0 · Answer 3 · answered Feb 16 '13 at 01:45

0

Not that clean though..

//Untested Code
Node n = Node.objects.get(name="x")

//This would return To Node IDs' ordered by weight

n.edges.filter(from_node = n).values_list('to_node', flat=True).order_by('weight')

answered Feb 16 '13 at 01:45

Raunak Agarwal

7,117
6
38
62

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

3 Answers3