Throoze Throoze - 1 year ago 400
SQL Question

Django - Filter a queryset by Max(date) year

I would like to know if I can get in a single query, All the objects of certain model where its date's year equals the year of the Max('date') of the model. For example, using the models from the Aggregation Django Docs, how can I get All the

s published in the year of the more recently published

All the examples in the docs filter by immediate values (
), but I need to use a calculated value over the same object in the same query.

Of course, I could do this by performing two queries: one for getting the max year, and a second one to filter by that year, but I think it should be possible to do it in a single query. It's just I haven't figured it out yet.

Thanks for your help!


Since some of you have given similar answers, I'm writing this update so my problem can be better understood.

This is my model:

class Expo(models.Model):

class Meta:
verbose_name= _('Expo')
verbose_name_plural = _('Expos')

name = models.CharField(max_length=255)
place = models.CharField(max_length=255, null=True, blank=True)
date = models.DateField()
bio = models.ForeignKey(Bio, related_name='expos')

I need "All the
s that happened in the latest year of the list of
s stored in my database"

To resolve this, I'm doing this:

from django.db.models import Max
max_year = Expo.objects.all().aggregate(Max('date'))['date__max'].year
expos = Expo.objects.filter(date__year=max_year)

But this, I understand that performs two queries on the database. I would like an expression that let me get the same result, but performing a single query.

I've tried as suggested:


But get the error:

FieldError: Join on field 'date' not permitted. Did you misspell 'year' for the lookup type?

I also have tried:


but I get the error:

FieldError: Cannot resolve keyword 'max_date' into field. Choices are: bio, date, id, items, name, place, max_date

Notice that it says that it can't resolve
, but it appears listed among the choices. Weird.

Again, Thanks a lot for your help! :)

Answer Source

Performing statement in a single query is no guarantee to improve performance, this is easy to understand if you try to write an agnostic RDBMS brand SQL single sentence for yours requirements. Also, you lost in readability.

In my opinion, you can see and elegant solution in this approach:

  1. Get last Expo by date .
  2. Do a simple filter query.

For your code:

max_year = Expo.objects.latest('date').date.year
expos = Expo.objects.filter(date__year=max_year)

Remember you can cache max_year, also create a DESC index over date can helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download