如何将这个复杂的查询转换为Django ORM等价查询

Question

型号：

Items(models.Model):
name = models.CharField(max_length=250, verbose_name="Item Name")
created_date = models.DateTimeField(auto_now_add=True)

Accounts(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE, null=True)
url_tag = models.CharField(max_length=200)
item = models.ForeignKey(Items, on_delete=models.CASCADE)

Comments(models.Model):
item = models.ForeignKey(Items, on_delete=models.CASCADE, null=True)
comment = models.TextField(null=True)
url_tag = models.URLField(null=True)
is_flagged = models.BooleanField(default=False, null=True)
response_required = models.BooleanField(default=True)
created_date = models.DateTimeField(auto_now_add=True)

Responses(models.Model):
response = models.TextField()
comment = models.ForeignKey(Comments, on_delete=models.CASCADE)
created_date = models.DateTimeField(auto_now_add=True)

Sql查询：

select c.id as comment_id, c.created_date, c.is_flagged, c.response_required 
from comments as c 
left join responses as r on c.id = r.comment_id
inner join items as i on i.id = c.item_id and r.comment_id is null 
left join accounts as a on 
(a.item_id = c.item_id and lower(a.url_tag) = lower(c.url_tag)
where c.id in (12, 32, 42, 54) 
ORDER BY c.created_date desc, comment_id desc;

我尝试过的：

filters = {
'id__in': [12, 32, 42, 54]
}
comment_objs = Comment.objects.filter(**filters)
.select_related('commentsresponses', 'item')
.order_by('-created_date', '-id')

我想在加入并应用过滤器后获得评论对象的列表。

也许我们可以在这里使用序列化程序，我不知道，因为我对django 没有太多经验

Answer 1

如果时间复杂性对您来说无关紧要，您可以使用原始SQL查询：

from django.db import connection
cursor = connection.cursor()
cursor.execute('''select c.id as comment_id, c.created_date, c.is_flagged, 
c.response_required 
from comments....''')