Django-Query:用Related Model字段注释Queryset

Question

我在Django和PostgreSQL中有以下Schema。

Class Person (models.Model):
name = models.CharField(max_length=255)
email= models.CharField(max_legth = 255)
created_at = models.DateTimeField()
Class PersonTask(models.Model):
person = models.ForeignKey(Person)
title = models.TextField()
created_at = models.DateTimeField()
Class PersonHistory(models.Model):
person = models.ForeignKey(Person)
note = models.TextField()
created_at = models.DateTimeField()

现在，我需要像Person的所有值一样查询DB，最新的PersonTask__title为max_task，最新PersonHistory__note为max_note

例如：

<Queryset: [
{name: "abc" ,email:"[e-mail]",created_at :"2019-01-02", max_task:"This is my latest tasktitle" , max_note: "This is my latest history note"},
{name: "abcd" ,email:"[e-mail]",created_at :"2019-03-02", max_task:"This is my latest tasktitle for abcd" , max_note: "This is my latest history note for abcd"}
]>

但是，我可以最大限度地获得的最新任务和最新历史的id

Person.objects.filter(customer_id= 1).
annotate( max_task = Max('persontask')).
annotate(max_note = Max('personhistory')).
order_by('-id')

或者使用以下查询的随机任务或注释文本

Person.objects.filter(customer_id= 1).
annotate( max_task = Max('persontask__title')).
annotate(max_note = Max('personhistory__note')).
order_by('-id')

如何解决这一问题？？

Answer 1

由于您没有提到这些模型之间的ForeignKeys，我怀疑Task和History在名为Person的字段中有FK到Person。

我会使用Subquery和OuterRef的组合来处理这个查询

from django.db.models import OuterRef, Subquery
result = (
Person.objects
.filter(customer_id=1)
.annotate(
task_title=Subquery(Task.objects.filter(person=OuterRef('pk')).order_by('-created_at').values('title')[:1]),
history_note=Subquery(HistoryNote.objects.filter(person=OuterRef('pk')).order_by('-created_at').values('note')[:1])
)
.order_by('-id')
)

Answer 2

如果任务和历史记录与人员相关，则需要模型之间的关系。像这样的东西。

class PersonTask(models.Model):
person = models.ForeignKey(Person, related_name="tasks", on_delete=models.CASCADE)
title = models.TextField()
created_at = models.DateTimeField()
class PersonHistory(models.Model):
person = models.ForeignKey(Person, related_name="histories", on_delete=models.CASCADE)
note = models.TextField()
created_at = models.DateTimeField()

然后你可以得到最后一个任务和最后一个历史：

person = Person.objects.get(name="name")
person.tasks.last()
person.histories.last()