Django queryset get max id's for a filter

Question

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?

latest you mean by time? if you have a creation time, just order by date and get the first result. — Bestasttung
– Bestasttung, Commented Sep 20, 2017 at 13:59
Latest by id is possible, but that will give me just one object. I want multiple objects in the end result based on the name. — Akamad007
– Akamad007, Commented Sep 20, 2017 at 14:01
@Bestasttung I want multiple objects in the output. Sorting by id and taking the latest won't work. — Akamad007
– Akamad007, Commented Sep 20, 2017 at 14:05

user8060120 · Accepted Answer · 2017-09-20 14:10:58Z

1

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)

answered Sep 20, 2017 at 14:10

user8060120

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

2 Comments

Akamad007 Over a year ago

I just saw this. Is there a more efficient way of doing this?

user8060120 Over a year ago

unfortunately i don't know, how add annotate column to the filter, but if you find the solution it will good, by the way, it is not bad as it look

Iain Shelvington · Accepted Answer · 2017-09-20 16:47:59Z

1

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

edited Sep 20, 2017 at 16:47

answered Sep 20, 2017 at 16:19

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Collectives™ on Stack Overflow

Django queryset get max id's for a filter

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related