django通过cocece从同一模型中获取最大长度值

Question

我正在尝试使用ORM编写以下原始查询。我不确定是否可能。

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

预期的结果就像以下内容一样，如果middle_name为null，请从具有相同first_name和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版本：3.12

Answer 1

通过使用django orm，您可以使用以下代码

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")
)

说明

1. models.OuterRef用于从子查询的父级查询中引用该字段。
2. - order_by中的前缀（“ - Midder_name_len”）用于降序顺序
3. .values（“ Midder_name”）用于仅选择midder_name值。
4. 切片[：1]是为了将子查询的结果限制为一个。

提示

• 您可以使用result.query检查ORM将为您生成的查询。