python - SQLAlchemy:如何正确使用group_by()(only_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 我收到此错误:

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '...' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

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

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

提前致谢

Answer 1

形成 greatest-n-per-group 的一种方法具有明确定义行为的查询将使用 LEFT JOIN，为每个 category_id 查找 MyModel 行，这些行没有具有更大 的匹配行发布日期:

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 和 MySQL 8(以及许多其他版本)中，您可以使用窗口函数来实现相同的目的:

sq = session.query(
        MyModel,
        func.row_number().
            over(partition_by=MyModel.category_id,
                 order_by=MyModel.published_date.desc()).label('rn')).\
    subquery()

my_model_alias = aliased(MyModel, sq)

rows = session.query(my_model_alias).\
    filter(sq.c.rn == 1).\
    all()

当然，如果您在联接中使用结果，您也可以使用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()