CODEWITHSUNDEEP
djangodjango-queryset
Akamad007
Akamad007

Reputation: 1601

Django queryset get max id's for a filter

I want to get a list of max ids for a filter I have in Django

class Foo(models.Model):
   name = models.CharField()
   poo = models.CharField() 

Foo.objects.filter(name__in=['foo','koo','too']).latest_by_id()

End result a queryset having only the latest objects by id for each name. How can I do that in Django?

Edit: I want multiple objects in the end result. Not just one object.

Edit1: Added __in. Once again I need only latest( as a result distinct) objects for each name.

Something like this. 

my_id_list = [Foo.objects.filter(name=name).latest('id').id for name in ['foo','koo','too']]
Foo.objects.filter(id__in=my_id_list)

The above works. But I want a more concise way of doing it. Is it possible to do this in a single query/filter annotate combination?

Upvotes: 1

Views: 2451

Answers (2)

Iain Shelvington
Iain Shelvington

Reputation: 32244

If you are using PostgreSQL you can do the following

Foo.objects.order_by('name', '-id').distinct('name')

MySQL is more complicated since is lacks a DISTINCT ON clause. Here is the raw query that is very hard to force Django to generate from ORM function calls:

Foo.objects.raw("""
    SELECT 
        *
    FROM
        `foo`
    GROUP BY `foo`.`name`
    ORDER BY `foo`.`name` ASC , `foo`.`id` DESC
""")

Upvotes: 1

user8060120
user8060120

Reputation:

you can try:

qs = Foo.objects.filter(name__in=['foo','koo','too'])
# Get list of max == last pk for your filter objects
max_pks = qs.annotate(mpk=Max('pk')).order_by().values_list('mpk', flat=True)
# after it filter your queryset by last pk
result = qs.filter(pk__in=max_pks)

Upvotes: 1

Related Questions