Order_by combined column in django

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?

order_by can take a field name in the subclasses, ie, Parent.objects.all().order_by('-bproxy__target__name', '-a__name'). This will yield the following query which will order based on A name and B Name

SELECT "tmp_parent"."id" FROM "tmp_parent" LEFT OUTER JOIN "tmp_bproxy" ON ("tmp_parent"."id" = "tmp_bproxy"."parent_ptr_id") LEFT OUTER JOIN "tmp_b" ON ("tmp_bproxy"."target_id" = "tmp_b"."id") LEFT OUTER JOIN "tmp_a" ON ("tmp_parent"."id" = "tmp_a"."parent_ptr_id") ORDER BY "tmp_b"."name" DESC, "tmp_a"."name" DESC

this will order with A first then B, if you want to order with respect to both A and B, consider @temunel answer, or use

    from django.db.models.functions import Coalesce

    Parent.objects.annotate(
        name=Coalesce("a__name", "bproxy__target__name")
    ).order_by('name')

this means that if a__name is null or does not exist, the value of bproxy__target__name will be used.

Note: not all databases have a COALESCE() and a__name and bproxy__target__name value should be of compatible type for ordering

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.

This almost sounds like an XY problem to me, but it is hard to tell without more information about what you are trying to achieve. As in, you are asking about how to do X, but you really need an answer how best to do Y. However, I will answer assuming that you need to do X.

From your query, Parent.objects.all(), you won't be able to order by name as you are only querying the table Parent. You will still need to look up the data you want from the A or BProxy (which will then allow you to cascade look-up B). My question is whether Parent should really be its own table or if you should be using an abstract table and/or a proxy table. I can't advise you on that without more knowledge about what you are trying to do but you can read though some articles on it.

One approach to help converting Parent into its A or BProxy children would be to make a helper function like such:

class Parent(models.Model):
    ...
    def child(self):
        try:
            return A.objects.get(id=self.id)
        except A.DoesNotExist:
            return BProxy.objects.get(id=self.id)

As written, it's not perfect as it will throw a confusing error of BProxy.DoesNotExist if the Parent row does not match anything in either A or BProxy. However, it might be OK if all Parents will always be one or the other. Now, you can convert a QuerySet into a list of child objects:

children = [x.child() for x in m.Parent.objects.all()]

This does mean that all rows queried will be retrieved at this time and you will have to sort it in Python, but it is relatively straight-forward at this point. To help make BProxy look more like A, you can define a property on it as such:

class BProxy(Parent):
    ...
    @property
    def name(self):
        return self.target.name

Now you can use sorted() on the name property they both have:

    ordered_children = sorted(children, key=lambda x: x.name)
Back to Top