SQLAlchemy:查询每个y的最大金额

Question

我有一个带有日期列的模型Photo。我正在制作一个照片库，有一个概览视图，显示了每年最多5张照片，有照片。你知道吗

目前，我有这个查询，并让它循环通过手工制作的年度列表。
（即for year in range(2000, 2016):）

Photo.query \
.filter(extract('year', Photo.date)==year) \
.order_by(Photo.date) \
.limit(5) \
.all()

有没有更有效的方法（而不是15个查询）？还有，但不太重要的是，有没有一种方法可以根据照片存在的年份对年份进行排序（作为使用硬编码列表的替代方法）？你知道吗

更新：我正在使用sqlite3

Answer 1

假设date是唯一的，下面的查询应该适用于sqlite：

# define aliases as we will need to join the table on itself
p1 = Photo
p2 = db.aliased(Photo, name='p2')

q = (
    p1.query
    # join *Photo* on itself and count the *rank* of current photo by counting the photos with date before it in the same year
    .join(p2, sa.and_(
        sa.func.extract("year", p1.date) == sa.func.extract("year", p2.date),
        p1.date >= p2.date,
    ))
    .group_by(p1.id)
    .having(sa.func.count() <= 5)
    .order_by(p1.date)  # This will automatically order by year and rank
)

如果date不是唯一的，但是几乎是唯一的，则结果不一定总是5行，但可以是多行或少行。如果这些真的是date值（没有时间成分），让我知道-应该很容易得到一个更健壮的解决方案。你知道吗