Django - Query : Annotate Queryset with Related Model fields

Question

I have following Schema in Django with PostgreSQL.

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

Now I need to query the DB like all values of Person with latest PersonTask__title as max_task and latest PersonHistory__note as max_note

Eg:

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

But, I could max get is either id of Latest Task and Latest History by

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

Or a random task or note texts using below query

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

How this can be tackled??

Kryštof Řeháček · Accepted Answer · 2020-04-17 17:35:18Z

1

As you did not mention the ForeignKeys between these models, I suspect that Task and History have FK to Person in field named person.

I would use Subquery with combination of OuterRef to tackle this query

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

answered Apr 17, 2020 at 17:35

Kryštof Řeháček

2,5531 gold badge19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JithPS Over a year ago

Yeah you are right about the DB models. I have updated the Models. But May I know, which version of Django will support this one?

Kryštof Řeháček Over a year ago

I've looked to the django docs and it looks it's supported even in 1.11 :-) docs.djangoproject.com/en/1.11/ref/models/expressions/…

JithPS Over a year ago

Thank you so much. Yeah I have tried them and are working fine

JithPS Over a year ago

I did :-) . One more doubt is there any option to get date along with the title or task??

Kryštof Řeháček Over a year ago

Sure, you can take more values from the subquery like Subquery(Task.objects.filter(person=OuterRef('pk')).order_by('-created_at').values('title', 'created_at')[:1])

|

bertinhogago · Accepted Answer · 2020-04-17 17:34:51Z

0

If tasks and history are related with person you need a relationship between your models. something like this.

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

And then you could get last task and last history:

person = Person.objects.get(name="name")

person.tasks.last()
person.histories.last()

answered Apr 17, 2020 at 17:34

bertinhogago

3492 silver badges6 bronze badges

1 Comment

JithPS Over a year ago

Models Have been updated. But I need the latest task and history as an annotated qiueryset for all the lead objects

Collectives™ on Stack Overflow

Django - Query : Annotate Queryset with Related Model fields

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related