Django get max length value from same model with Coalesce

Question

I'm trying to write the following raw query with the ORM. I'm not sure is it possible or not.

select first_name,
       middle_name,
       COALESCE(middle_name, (
           select middle_name
           from contacts c2
           where c2.first_name = c1.first_name
             and c2.last_name = c1.last_name
             and c2.middle_name is not null
           order by length(c2.middle_name) desc
           limit 1
       )
           ) expected,
       last_name
from contacts c1

The expected result is like the following, if middle_name is null, get the middle name from another record that has the same first_name and last_name.

id| first_name | middle_name | expected | last_name
1 | ahmet      |   <NULL>    |  burak   |   ozyurt
2 | ahmet      |   burak     |  burak   |   ozyurt

class Contact(models.Model):
    first_name = models.CharField(max_length=250)
    last_name = models.CharField(max_length=250, null=True, blank=True)
    middle_name = models.CharField(max_length=250, null=True, blank=True)

DB: Postgres
Django Version: 3.12

Answer 1

By using the django ORM, you can perform the same query by using the following code

from django.db import models
from django.db.models.functions import Coalesce, Length

matched_middle_name_queryset = Contact.objects.filter(
    first_name=models.OuterRef("first_name"),
    last_name=models.OuterRef("last_name"),
    middle_name__isnull=False,
).annotate(
    middle_name_len=Length("middle_name")
).order_by("-middle_name_len").values("middle_name")[:1]

result = Contact.objects.annotate(
    matched_middle_name=models.Subquery(matched_middle_name_queryset)
    expected=Coalesce(
        models.F("middle_name")
        models.F("matched_middle_name"),
    ).values("id", "first_name", "middle_name", "expected", "last_name")
)

Explanations

1. models.OuterRef is used for referring to the field from the parent query of a subquery.
2. - prefix in the order_by("-middle_name_len") is for descending order
3. .values("middle_name") is for selecting only middle_name values.
4. the slicing [:1] is for limiting the result from the subquery to be one.

Tips

• You can use result.query to inspect what query the ORM will generate for you.