[Answered ]-Is it possible to get max id in filtered and limited queryset without fetching?

by

1👍

You can just use:

from django.db.models import Max

MyModel.objects.filter(foo=2).aggregate(max=Max('id'))['max']

this will make a single query that will only return the greatest id for all MyModels with foo=2.

As for getting the n-th, you can work with a union queryset, like:

qs = MyModel.objects.filter(foo=2)
list(
    qs.order_by('id')[limit - 1 : limit].union(qs.order_by('-id')[:1], all=True)
)[0].id

This will retrieve at most two records, one with the limit, and the last one. We then cast these to a list and return the first of the two. If the first one with the limit is missing, we get the last one (the second queryset). But these are retrieved in the same database query.

👤willeM_ Van Onsem

0👍

If all you need is the max id, it’s very basic:

SELECT max(id) AS id
FROM   tbl
WHERE  foo = 2;

If you need more columns or the whole row:

SELECT *
FROM   tbl
WHERE  foo = 2
ORDER  BY id DESC
LIMIT  1;

IF id can be null, you’ll probably want:

...
ORDER  BY id DESC NULLS LAST
...

See:

If the table is big and performance matters, a multicolumn index on (foo, id DESC) would be ideal. (Or (foo, id DESC NULLS LAST) if id can be null.) Just on (foo, id) is almost as good. And if there are only few rows for the same foo, just on (foo) is typically good enough.

👤Erwin Brandstetter

Leave a comment