CODEWITHSUNDEEP
pythondjango
David
David

Reputation: 147

Order By with Foreign Object Field if it exists

I am trying to do a query where I would order by foreign object if there is one - or by a local field.

Example:

class AuditorData(Base):
    auditor_weight = models.FloatField()
    audited_object = models.ForeignKey('MyObject', related_name='audits')

class MyObject(Base):
    weight = models.FloatField()

Basically I want to do order by Max(audits.auditor_weight) but if there is no audits, it will order by 'weight'

How could I create a query to do so

I tried to do use annotation and aggregation, with coalesce order_by, but it doesn't work.

Any solution?

Upvotes: 0

Views: 63

Answers (1)

ruddra
ruddra

Reputation: 51988

You can try like this using conditional statement:

from django.db.models import Count, Max, Case, When, FloatField, F

MyObject.objects.all().annotate(count=Count('audits')).annotate(
    max=Case(
        When(count=0, then=F('weight')),
        When(count__gt=0, then=Max('audits__auditor_weight')),
        output_field=FloatField()
        )
    )
).order_by('max')

Upvotes: 1

Related Questions