Django order a query by instance of ManyToMany

I have a model A with a ForeignKey to B:

class A(models.Model):
    b = models.ForeignKey(B, on_delete=models.CASCADE)

A ManyToMany relationship with an extra field that weight any B and C relation:

class B2C(models.Model):
    b = models.ForeignKey(B, on_delete=models.CASCADE)
    c = models.ForeignKey(C, on_delete=models.CASCADE)
    weight = models.IntegerField(default=0)

And I need to order the A model (A.objects.filter(…)) using the weight of B2C for a given instance of C.

For only one A instance I can do :

# Example of C instance
c = C.objects.get(pk=1)

# Single instance of A
a = A.objects.get(pk=1)

# Getting the weight for this instance
# A => B => B2C WHERE metier=metier
weight = a.b.b2c_set.get(c=c)

But I don’t know how to do apply this on a queryset (like using it in a annotate).

During my research I’ve found theses F(), ExpressionWrapper, SubQuery, annotate but I can’t figure out how to use them for my problem.

Thanks for reading :)

Answer

As you already notice, you need to use a Subquery [Django docs] to annotate the weight. You can use OuterRef to refer to the outer queries b while filtering and also use Coalesce [Django docs] just in case to provide a default value:

from django.db.models import OuterRef, Subquery
from django.db.models.functions import Coalesce


weight_subquery = B2C.objects.filter(b=OuterRef('b'), c=given_c_instance)

queryset = A.objects.annotate(
    weight=Coalesce(Subquery(weight_subquery.values('weight')[:1]), 0)
).order_by('weight')