如何将Django ORM逻辑转为SQL并与现有查询高效整合？

现有Django模型及可运行的原生SQL查询，需要将指定ORM逻辑转换为原生SQL并与原有查询整合，但尝试的子查询方案无法正常运行且效率低下，寻求正确且高效的解决方案。

Django 模型定义 #

Apartment 模型 #

class Apartment(models.Model):    
    name = models.CharField(max_length=250)

Review 模型 #

class Review(models.Model):
    apartment = models.ForeignKey(Apartment, on_delete=models.CASCADE, null=True)
    review = models.TextField(null=True)
    author = models.CharField(max_length=255, null=True)
    review_date = models.DateField(null=True)

Response 模型 #

class Response(models.Model):
    response = models.TextField()
    review = models.ForeignKey(Review, on_delete=models.CASCADE)
    responded_on = models.DateTimeField(auto_now_add=True)
    author = models.CharField(max_length=255)

Note 模型 #

class Note(models.Model):
    review = models.ForeignKey(Review, on_delete=models.CASCADE, null=True)
    current_user = models.IntegerField(null=True)
    modified_on = models.DateTimeField(
      auto_now=True,verbose_name='Last Modified on'
    )

UnreadNote 模型 #

class UnreadNote(models.Model):
    current_user = models.IntegerField(null=True)
    review = models.ForeignKey(Review, on_delete=models.CASCADE)
    note = models.ForeignKey(
      Note, on_delete=models.CASCADE, 
      verbose_name='Last Read Note'
    )
    modified_on = models.DateTimeField(
      verbose_name='Modified Date Of Last Read Note'
    )

原有可运行原生SQL #

SELECT rw.id, a.name apartment_name, p.id property_id, rw.review_date, 
un.modified_on modified_on 
FROM review rw LEFT JOIN response re ON rw.id = re.review_id 
INNER JOIN apartment a ON a.id = rw.apartment_id 
INNER JOIN note n ON n.review_id = rw.id 
AND n.current_user != 321 AND n.current_user IS NOT NULL
WHERE  rw.apartment_id IN (23, 432, 667, 123) 
AND rw.review_date BETWEEN '2023-02-01' AND '2023-04-30' 
GROUP BY  rw.id 
ORDER BY rw.review_date desc

需要转换的ORM逻辑 #

该函数用于计算指定评论下当前用户的未读笔记数量：

def get_note_count_by_review(review_id, user_id, start_date="", end_date=""):
    unread_note = UnreadNote.objects.filter(
       review_id=review_id, current_user=user_id
    ).order_by("-modified_on").first()
    fields = {'review_id': review_id}
    fields.update({
         'modified_on__gt': unread_note.modified_on} if unread_note 
         else {}
    )
    fields.update(
        {'review__review_date__gte': start_date,
        'review__review_date__lt': end_date} 
        if start_date and end_date else {}
    )
    unread_note_count = Note.objects.filter(**fields).exclude(
       current_user=user_id
    ).exclude(
       current_user__isnull=True
    ).count()
    return unread_note_count

用户尝试的错误SQL #

SELECT rw.id, a.name apartment_name, p.id property_id, rw.review_date, 
un.modified_on modified_on 
FROM review rw LEFT JOIN response re ON rw.id = re.review_id 
INNER JOIN apartment a ON a.id = rw.apartment_id 
INNER JOIN note n ON n.review_id = rw.id 
AND n.current_user != 321 AND n.current_user IS NOT NULL 
AND modified_on > (SELECT modified_on FROM 
unread_note WHERE current_user=321 ORDER BY modified_on LIMIT 1)
WHERE  rw.apartment_id IN (23, 432, 667, 123) 
AND rw.review_date BETWEEN '2023-02-01' AND '2023-04-30' 
GROUP BY  rw.id 
ORDER BY rw.review_date desc

解决方案 #

1. ORM对应的正确SQL逻辑 #

ORM核心逻辑拆解：

• 获取当前用户针对指定评论的最新UnreadNote记录
• 存在该记录时，统计评论下修改时间晚于该记录的笔记；不存在时统计所有符合条件的笔记
• 排除当前用户创建的笔记及无创建者的笔记
• 可选按评论日期范围过滤

单条评论的对应SQL（以review_id=X、user_id=321为例）：

SELECT COUNT(n.id) AS unread_count
FROM note n
WHERE n.review_id = X
  AND n.current_user != 321
  AND n.current_user IS NOT NULL
  AND (
    n.modified_on > (
      SELECT MAX(modified_on)
      FROM unread_note
      WHERE review_id = X AND current_user = 321
    )
    OR NOT EXISTS (
      SELECT 1
      FROM unread_note
      WHERE review_id = X AND current_user = 321
    )
  )
  AND EXISTS (
    SELECT 1
    FROM review rw
    WHERE rw.id = n.review_id
      AND rw.review_date >= '2023-02-01'
      AND rw.review_date < '2023-04-30'
  );

2. 整合后的高效查询 #

通过LEFT JOIN预获取用户最新未读记录，用条件聚合计算未读数量，避免重复子查询：

SELECT 
  rw.id, 
  a.name apartment_name, 
  p.id property_id, 
  rw.review_date, 
  MAX(un.modified_on) AS last_read_modified_on,
  COUNT(
    CASE 
      WHEN n.current_user != 321 AND n.current_user IS NOT NULL
        AND (un.modified_on IS NULL OR n.modified_on > un.modified_on)
      THEN n.id 
      ELSE NULL 
    END
  ) AS unread_note_count
FROM review rw 
LEFT JOIN response re ON rw.id = re.review_id 
INNER JOIN apartment a ON a.id = rw.apartment_id 
LEFT JOIN note n ON n.review_id = rw.id
-- 预获取当前用户针对每个评论的最新未读记录
LEFT JOIN (
  SELECT review_id, current_user, MAX(modified_on) AS modified_on
  FROM unread_note
  WHERE current_user = 321
  GROUP BY review_id, current_user
) un ON un.review_id = rw.id 
WHERE  
  rw.apartment_id IN (23, 432, 667, 123) 
  AND rw.review_date BETWEEN '2023-02-01' AND '2023-04-30'
GROUP BY  rw.id, a.name, p.id, rw.review_date
ORDER BY rw.review_date desc

关键优化点 #

• 用分组子查询+LEFT JOIN获取用户最新未读记录，避免多次重复查询
• 条件聚合COUNT(CASE...)直接计算未读数量，无需额外子查询
• 为unread_note表添加(review_id, current_user, modified_on)联合索引，提升关联效率
• 为note表添加(review_id, current_user, modified_on)联合索引，加速过滤和聚合

内容的提问来源于stack exchange，提问作者Ahtisham