Django查询器根据日期返回每个项目的第一个外键 [英] Django queryset to return first of each item in foreign key based on date
问题描述
需要通过第一本书(通过日期字段)获取每个作者的查询语言(与外部关键字相关)...是否有Django ORM方式来执行此操作(不需要自定义SQL,但可以接受) p>
*编辑:请注意,只能使用像Postgresql这样的现代开放源代码后端的答案是可以接受的。基于ORM的解决方案优于纯粹的自定义sql查询)
模型
class Book(Model):
date = Datefield()
author = ForeignKey )
类作者(模型):
name = CharField()
Book.objects.filter(?)
如果您使用PostgreSQL或其他DB后端支持 DISTINCT ON
有一个很好的解决方案:
Books.objects.order_by('author' '-date')。distinct('author')
否则我不知道一个解决方案只有一个查询。但您可以尝试以下方式:
from django.db.models import Q,Max
import operator
books = Book.objects.values('author_id')。annotate(max_date = Max('date'))
filters = reduce(operator.or_,[(Q(author_id = b ['author_id '])&
Q(date = b ['max_date']))for b in books])
queryset = Books.objects.filter(filters)
使用.values()和.annotate()的组合,我们由作者分组,并从该作者注释所有图书的最新日期。但结果是一个dict而不是一个查询。
然后,我们构建一个SQL语句,如 WHERE author_id = X1 AND date = Y1或author_id = X2 AND date = Y2 ...
。现在第二个查询很简单。
need to get a queryset with the first book (by a date field) for each author (related to by foreign key) ...is there a Django ORM way to do this (without custom SQL preferred but acceptable)
*Edit: Please note that an answer that works using only a modern open source backend like Postgresql is acceptable ..still ORM based solution preferred over pure custom sql query)
Models
class Book(Model):
date = Datefield()
author = ForeignKey(Author)
class Author(Model):
name = CharField()
Book.objects.filter(??)
If you use PostgreSQL or another DB backend with support for DISTINCT ON
there is a nice solution:
Books.objects.order_by('author', '-date').distinct('author')
Otherwise I don't know a solution with only one query. But you can try this:
from django.db.models import Q, Max
import operator
books = Book.objects.values('author_id').annotate(max_date=Max('date'))
filters = reduce(operator.or_, [(Q(author_id=b['author_id']) &
Q(date=b['max_date'])) for b in books])
queryset = Books.objects.filter(filters)
With the combination of .values() and .annotate() we group by the author and annotate the latest date of all books from that author. But the result is a dict and not a queryset.
Then we build a SQL statement like WHERE author_id=X1 AND date=Y1 OR author_id=X2 AND date=Y2...
. Now the second query is easy.
这篇关于Django查询器根据日期返回每个项目的第一个外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!