CODEWITHSUNDEEP
djangodjango-rest-framework
Shezan Kazi
Shezan Kazi

Reputation: 4660

Django Rest Framework Filter related model field

I have trouble giving this a proper title.

These are my models:

class User(models.Model):
    # standard django User model from django.contrib.auth.models


class Person(models.Model):
    name = models.CharField(...)


class Note(models.Model)
    person = models.ForeignKey(Person, related_name='PersonNote')
    ower = models.ForeignKey(User)
    date_added = models.DateTimeField(auto_now_add=True)

I would like to filter all persons, whose last note from the User with ID = 5 has the action = 'helloed'.

Getting all persons whose note = 'helloed' and note owner = 5 works like this:

Person.objects.filter(PersonNote__action='helloed', PersonNote__owner=5)

However the above does not take into account, that only such Persons are returned whose last note has the action 'helloed', as long as 'helloed' was one of its note actions.

The pseudo SQL is perhaps like this:

SELECT 
    name from person,
    action, date_added from note,
    id from user
WHERE
    1 = 1
    AND action = 'helloed'
    AND id = 5
    AND date_added = MAX(date_added)

I understand this can be done via raw sql queries, but I would like to insert this into a viewset and thus I would prefer the django ORM way.

Upvotes: 0

Views: 132

Answers (1)

JPG
JPG

Reputation: 88639

If I understood your question correctly, this might solve your problem

from django.db.models import Max

max_date = Note.objects.aggregate(max_date=Max('date_added'))['max_date']
Person.objects.filter(PersonNote__action='helloed',
                      PersonNote__ower_id=5,
                      PersonNote__date_added=max_date)



Update-1
This will do a group by operation,

from django.db.models import Max

Note.objects.values('person__name').annotate(last_date=Max('date_added'))



Update-2

from django.db.models import Max

person_name = [agg['person__name'] for agg in Note.objects.values('person__name').annotate(last_date=Max('date_added'))]
Person.objects.filter(name__in=person_name)

Upvotes: 1

Related Questions