How to order by a field from the `through` table for a M2M relationship in Django?

Question

I have two models — Note and Pinboard — with a many to many relationship in a Django app. Those two models are related trough another model — Pin — so I can store additional information about the relationship.

I want to show the related Note instances in the DetailView for Pinboard. That's not the problem. But I want to prefetch the notes and also order them on the position field from the through table.

Any hints on how to archive this (prefetch + ordering on third table)?

Example

This is what I have so far… it works in the sense, that I don't have to query for each entry, but I found no way to order the Note instances by their position without more queries for each instance.

Models

from django.db import models


class Note(models.Model):

    title = models.CharField(max_lenght=200)

    content = models.TextField()


class Pinboard(models.Model):

    title = models.CharField(max_lenght=200)

    notes = models.ManyToManyField(
        Note, blank=True, related_name='pinboards', through='Pin'
    )


class Pin(models.Model):

    class Meta:
        ordering = ['position', ]
        get_latest_by = 'position'

    pinboard = models.ForeignKey(Pinboard, related_name='note_pins')

    note = models.ForeignKey(Note, related_name='pinboard_pins')

    position = models.PositiveSmallIntegerField(default=0)

View

from django.views.generic import DetailView


class PinboardDetailView(DetailView):

  model = Pinboard

  queryset = Pinboard.objects.prefetch_related('notes')

Template

{% extends 'base.html' %}
{% block content %}
<h1>{{ pinboard.title }}</h1>
{% if pinboard.notes.all.exists %}
    <ol>
    {% for note in pinboard.notes %}
        <li>{{ note.title }}</li>
    {% endfor %}
    </ol>
{% else %}
    <p>Nothing here yet…</p>
{% endif %}
{% endblock content %}

Answer 1

I suggest you use a Prefetch object.

class PinboardDetailView(DetailView):
    model = Pinboard
    queryset = Pinboard.objects.prefetch_related(
        Prefetch(
            'notes',
            Note.objects.order_by('pinboard_pins__position'),
        )
    )

By the way, you don't need to use prefetch_related at all on a DetailView as it will result in the same number of queries.

Plus, since you're already fetching the pinboard.notes I suggest you use {% if pinboard.notes.all %} instead of {% if pinboard.notes.all.exists %}.