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 :)
Advertisement
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')