django - Why is Model.objects.filter returning an empty query?

Question

I am reading an article on django about making queries over here. I have the following snippets.

>>> Entry.objects.first().pub_date
datetime.date(2021, 8, 18)
>>> Entry.objects.first().mod_date
datetime.date(2021, 8, 18)

But if I try the following I get an empty queryset.

Entry.objects.filter(pub_date__year=F('mod_date__year'))

Why is it not working. Is this a bug?

标签： djangodjango-queryset

Answer 1

It seems django handles __year and the other __ date extractions differently with annotate or F expressions, compared to when you use it in a filter.

For example in Postgres, the query generated with filter(pub_date__year=F('mod_date__year')) is:

WHERE EXTRACT(\'year\' FROM pub_date") = ("mod_date")

which would result with an error. But if used with an integer year like filter(pub_date__year=2021):

WHERE "pub_date" BETWEEN 2021-01-01 AND 2021-12-31'

USING EXTRACT:

To solve this, try to use Extract to generate the correct filter:

from django.db.models.functions import Extract


Entry.objects.filter(pub_date__year=Extract('mod_date', 'year'))

Which will generate this query:

WHERE EXTRACT(\'year\' FROM "pub_date") = (EXTRACT(\'year\' FROM "mod_date"))'