SQLAlchemy:如何正确使用group_by（）（仅限于\u full_group_by）？

Question

我正试图将SQLAlchemy的group_by()函数与mysql+mysqlconnector引擎一起使用：

rows = session.query(MyModel) \
        .order_by(MyModel.published_date.desc()) \
        .group_by(MyModel.category_id) \
        .all()

它可以很好地使用SQLite，但是对于MySQL，我得到了以下错误：

^{pr2}$

我知道how to solve it in plain SQL，但我想利用SQLAlchemy的优点。在

SQLAlchemy的正确解决方案是什么？在

提前谢谢

Answer 1

形成具有良好定义行为的greatest-n-per-group查询的一种方法是使用LEFT JOIN，在每个category_id中查找没有匹配行的published_date行：

my_model_alias = aliased(MyModel)

rows = session.query(MyModel).\
    outerjoin(my_model_alias,
              and_(my_model_alias.category_id == MyModel.category_id,
                   my_model_alias.published_date > MyModel.published_date)).\
    filter(my_model_alias.id == None).\
    all()

这将适用于任何SQL DBMS。在SQLite 3.25.0和MySQL8（以及许多其他版本）中，您可以使用窗口函数来实现相同的效果：

^{pr2}$

当然，您也可以使用GROUP BY，如果您在联接中使用结果：

max_pub_dates = session.query(
        MyModel.category_id,
        func.max(MyModel.published_date).label('published_date')).\
    group_by(MyModel.category_id).\
    subquery()

rows = session.query(MyModel).\
    join(max_pub_dates,
         and_(max_pub_dates.category_id == MyModel.category_id,
              max_pub_dates.published_date == MyModel.published_date)).\
    all()