Django annotate conditional value-CodePudding

Lets say i have a model like this :

class modelA(models.model):
    name = models.Charfield()

class modelB(models.model):
    model_A = models.ForeignKey('modelA', on_delete=models.CASCADE)
    value = models.IntegerField()

class modelC(models.model):
    model_A = models.ForeignKey('modelA', on_delete=models.CASCADE)
    value = models.IntegerField()

How can i annotate the query so i can achieve something like this :

q = modelA.objects.all().annotate(value= # IF THERE IS modelC exist with related modelA THEN modelC.value ELSE modelB.value #)

CodePudding user response：

Annotate your query with the value from each related model, I used Max here to get the greatest from each relationship as it's not clear how you handle multiple related rows of the same model. Then use the database function Coalesce to get the first non-null value from these previous annotations

modelA.objects.annotate(
    max_b=Max('modelb__value'), 
    max_c=Max('modelc__value')
).annotate(
    value=Coalesce('max_c', 'max_b')
)

CodePudding user response：

modelA can have multiple modelB and modelC objects, but since you are asking for the existence of either model, I will assume there can only be one of them, and take only the first value. First, you need to annotate the related values using Subquery, and after that, evaluate the value with sql switch-case:

from django.db.models import Case, When, Subquery, OuterRef, IntegerField


modelc_objects = modelC.objects.filter(model_A_id=OuterRef('pk'))
modelb_objects = modelB.objects.filter(model_A_id=OuterRef('pk'))

q = modelA.objects.all().annotate(
    c_value=Subquery(modelc_objects.values('value')[:1]),
    b_value=Subquery(modelb_objects.values('value')[:1]),
    value=Case(
        When(c_value__isnull=False, then=F('c_value')),
        When(b_value__isnull=False, then=F('b_value')),
        default=F('c_value'),
        output_field=IntegerField()
    )
)