order_by combined column in django

Question

I have two models who inherit from another model. Example:

class Parent(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False, verbose_name="ID")


class A(Parent):
    name = models.CharField(max_length=255, verbose_name="Name")


class BProxy(Parent):
    target = models.OneToOneField('B', on_delete=models.CASCADE)


class B(models.Model):
    name = models.CharField(max_length=255, verbose_name="Name")

My query currently looks like this:

Parent.objects.all()

In my serializer, I check which subclass the parent object is (hasattr(obj, 'a')) and then use either name = obj.a.name or name = obj.b.target.name for the serialized data.

But now I would like to sort the queryset for the output. Normally I would use Parent.objects.all().order_by('name') here. But the name is in the subclasses.

Would it be possible to combine the “name” columns of the two subclasses and then sort by them? Or is there another solution?

Answer 1

To sort the Parent objects based on the name field of its subclasses, you need to do annotating the queryset with the name field from both subclasses and then order by this annotated field.

This is how you can do it:

from django.db.models import Case, When, Value, CharField

queryset = Parent.objects.annotate(
    name=Case(
        When(a__isnull=False, then='a__name'),
        When(bproxy__isnull=False, then='bproxy__target__name'),
        default=Value(''),
        output_field=CharField(),
    )
).order_by('name')

I hope this will solve your issue.