how to use __year and __in in the same query?

Question

So here's what I'm trying to do.
I've a list with years inside, for instance years = [2002, 2003, 2004]
and I've a SomethingModel with a DateField

I want to do a query that will return me all the objects that belongs to that year:

I known this work:

 SomethingModel.objects.filter(date__year=2003) 
 SomethingModel.objects.filter(date__in=[list with dates])

So I've tried this:

SomethingModel.objects.filter(date__year__in=years)

but this return me this error:

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

Does anyone has any idea how to do this? In a direct way..

Thanks!

Answer 1

You can't, if you look at the queryset documentation

Entry.objects.filter(pub_date__year=2005)

becomes the SQL equivalent:

SELECT ... WHERE pub_date BETWEEN '2005-01-01' AND '2005-12-31 23:59:59.999999';

So you can't mix __in and __date conceptually. You can't mix suffixes anyway since the first "suffix" will be interpreted as a non-existent relationship.

You'll need to use a less than filter and a greater than filter or, if the list isn't contiguous, an extra where field, something like:

SomethingModel.objects.extra(where=["YEAR(date) IN (" + ",".join([str(x) for x in [2003, 2008, 2010]]) + ")"])