SQLAlchemy:如何正确使用group_by()(only_full_group_by)? [英] SQLAlchemy: How to use group_by() correctly (only_full_group_by)?
问题描述
我正在尝试将 SQLAlchemy 的group_by()
函数与mysql+mysqlconnector
引擎一起使用:
I'm trying to use the group_by()
function of SQLAlchemy with the mysql+mysqlconnector
engine:
rows = session.query(MyModel) \
.order_by(MyModel.published_date.desc()) \
.group_by(MyModel.category_id) \
.all()
它在SQLite上正常工作,但是对于MySQL,我会收到此错误:
It works fine with SQLite, but for MySQL I get this error:
[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
我知道如何,但是我想利用SQLAlchemy的优势.
I know how to solve it in plain SQL, but I'd like to use the advantages of SQLAlchemy.
SQLAlchemy的正确解决方案是什么?
What's the proper solution with SQLAlchemy?
预先感谢
推荐答案
形成 greatest-n-per-group 具有明确定义的行为的查询将使用LEFT JOIN
,查找MyModel
行没有匹配行且published_date
更大的每个category_id
:
One way to form the greatest-n-per-group query with well defined behaviour would be to use a LEFT JOIN
, looking for MyModel
rows per category_id
that have no matching row with greater 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和MySQL 8(以及许多其他版本)中,您可以使用窗口函数来实现相同的功能:
This will work in about any SQL DBMS. In SQLite 3.25.0 and MySQL 8 (and many others) you could use window functions to achieve the same:
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
:
Of course you could use GROUP BY
as well, if you then use the results in a join:
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()
这篇关于SQLAlchemy:如何正确使用group_by()(only_full_group_by)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!