Flask-SQLAlchemy结合PostgreSQL按name取最新行及多表关联
单表场景:为每个name取最新时间戳的记录
原有distinct写法失效的原因
PostgreSQL的DISTINCT ON(字段)有强制语法要求:该字段必须出现在ORDER BY的最左侧,再配合时间降序排序,才能保证每个分组下取到的第一条就是最新记录。原有写法没有把name放在排序的第一个位置,不符合语法规则,因此返回结果不符合预期。
修正后的单表ORM写法(PostgreSQL专用,效率更高)
from sqlalchemy import desc latest_single_table = MyModel.query \ .distinct(MyModel.name) \ .order_by(MyModel.name, desc(MyModel.time)) \ .all() # 如需指定返回字段,搭配with_entities使用即可 latest_single_table = MyModel.query \ .with_entities(MyModel.name, MyModel.xxx, MyModel.yyy) \ .distinct(MyModel.name) \ .order_by(MyModel.name, desc(MyModel.time)) \ .all()
通用窗口函数写法(适配多数据库,方便后续扩展)
如果不想绑定PostgreSQL特有语法,可以用ROW_NUMBER()窗口函数实现,逻辑和原生SQL完全对齐:
from sqlalchemy import func, desc # 构造带行号的子查询 row_num_subq = MyModel.query \ .with_entities( MyModel.name, MyModel.xxx, MyModel.yyy, # 按name分组、时间降序后生成行号,组内最新记录rn=1 func.row_number().over( partition_by=MyModel.name, order_by=desc(MyModel.time) ).label('rn') ) \ .subquery() # 筛选行号为1的记录 latest_records = db.session.query( row_num_subq.c.name, row_num_subq.c.xxx, row_num_subq.c.yyy ).filter(row_num_subq.c.rn == 1).all()
双表关联场景:关联两个表各自的最新记录
直接复用上面的窗口函数逻辑,把两个表的最新记录子查询构造好后关联即可,完全匹配你编写的原生SQL逻辑:
from sqlalchemy import func, desc # 构造sample_table_1(对应MyModel)的最新记录子查询 subq_m = MyModel.query \ .with_entities( MyModel.name, MyModel.xxx.label('m_xxx'), MyModel.yyy.label('m_yyy'), func.row_number().over( partition_by=MyModel.name, order_by=desc(MyModel.time) ).label('rn') ) \ .subquery() # 构造sample_table_2(对应MyModel2)的最新记录子查询 subq_n = MyModel2.query \ .with_entities( MyModel2.name, MyModel2.xxx.label('n_xxx'), MyModel2.yyy.label('n_yyy'), func.row_number().over( partition_by=MyModel2.name, order_by=desc(MyModel2.time) ).label('rn') ) \ .subquery() # 关联两个子查询,筛选各自rn=1的最新记录 final_result = db.session.query( subq_m.c.name, subq_m.c.m_xxx, subq_m.c.m_yyy, subq_n.c.n_xxx, subq_n.c.n_yyy ).join( subq_n, subq_m.c.name == subq_n.c.name ).filter( subq_m.c.rn == 1, subq_n.c.rn == 1 ).all()
小提示:你之前编写的原生SQL有两处小笔误:SELECT部分的
m.yyy后面缺少逗号,PARTITION BY里的container_name需要替换为实际的name字段名,执行前注意修正。
内容的提问来源于stack exchange,提问作者velo_fred
相关产品推荐
相关产品推荐

