Django-按Max（date）年过滤查询集 [英] Django - Filter a queryset by Max(date) year
问题描述
我想知道是否可以在单个查询中找到某个特定模型的所有对象，这些对象的日期年份等于该模型的最大值（日期）年份。例如，使用 Aggregation Django Docs 中的模型我可以获取最近出版的 Book 年份中出版的所有 Book 吗？
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?
docs中的所有示例均按立即值（ pubdate__year = 2006 ）进行过滤，但我需要对同一查询中的同一对象使用计算所得的值
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.
感谢您的帮助！
由于其中一些人给出了类似的答案，因此我正在编写此更新程序，以便可以更好地理解我的问题。
Since some of you have given similar answers, I'm writing this update so my problem can be better understood.
这是我的模型：
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')
我需要在我的数据库中存储的 Expo s列表的最近一年中发生的所有 Expo ;
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'))
但是出现错误：
FieldError: Join on field 'date' not permitted. Did you misspell 'year' for the lookup type?
我也尝试过：
Expo.objects.annotate(max_date=Max('date')).filter(date__year__gte=F('max_date__year'))
但出现错误：
FieldError: Cannot resolve keyword 'max_date' into field. Choices are: bio, date, id, items, name, place, max_date
请注意，它说不能解析'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! :)
推荐答案
在单个查询中执行语句不能保证提高性能，这很容易了解您是否尝试根据自己的需求编写一个不可知的RDBMS品牌SQL单句。另外，您可读性下降。
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:
- 按日期获取最后的
Expo。 - 执行简单的过滤器查询。
对于您的代码：
max_year = Expo.objects.latest('date').date.year
expos = Expo.objects.filter(date__year=max_year)
请记住，您可以 缓存 max_year，还可以在日期上创建 DESC 索引，
Remember you can cache max_year, also create a DESC index over date can helps.
这篇关于Django-按Max（date）年过滤查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
