CODEWITHSUNDEEP
sqldjangodjango-queryset
Throoze
Throoze

Reputation: 4038

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 Books published in the year of the more recently published Book?

All the examples in the docs filter by immediate values (pubdate__year=2006), 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!

EDIT:

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 Expos that happened in the latest year of the list of Expos 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:

Expo.objects.annotate(max_year=Max('date__year')).filter(date__year=F('max_year'))

But get the error:

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

I also have tried:

Expo.objects.annotate(max_date=Max('date')).filter(date__year__gte=F('max_date__year'))

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 'max_date', but it appears listed among the choices. Weird.

Again, Thanks a lot for your help! :)

Upvotes: 13

Views: 35530

Answers (3)

pramod24
pramod24

Reputation: 1116

Throoze try this...

 queryset=Expo.objects.annotate(max_date=Max('date'))
 queryset1=queryset.values().filter(date__gte=F('max_date'))

Upvotes: 2

Sunny Nanda
Sunny Nanda

Reputation: 2382

Here is how you can do something using a combination of Annotation and F object

To filter on Max date:

ModelClass.objects.annotate(max_date=Max('pubdate')).filter(pubdate=F('max_date'))

To filter on the year of Max date:

max_date = ModelClass.objects.latest("pubdate").pubdate
published_objs = ModelClass.objects.filter(pubdate__year=max_date.year)

There does not seem to be a way to filter on Max(date.year) in a single query. And as mentioned by @danihp, even a single query is not a guarantee of performance.

Upvotes: 2

dani herrera
dani herrera

Reputation: 51655

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.

Upvotes: 13

Related Questions