SQLAlchemy group_concat 有序值 [英] SQLAlchemy group_concat ordered values
问题描述
我想在 SQLAlchemy 中模拟 collect_set
的行为(使用 MySQL 连接器).即组 A 1,2,3
将与 2,1,3
相同.我有以下代码:
I want simulate the behaviour of collect_set
in SQLAlchemy (using MySQL connector). i.e. group A 1,2,3
would be the same as 2,1,3
. I have the following code:
res = db.session \
.query(T1.col1.label('col1'), func.group_concat(T1.col2.distinct()).label('col2_group')) \
.group_by(T1.col1) \
.all()
然而,默认情况下,SQLAlchemy 不会对组中的值进行排序,因此可能会发生重复.
However, by default SQLAlchemy does not order the values in the group, so duplicates may occur.
有没有办法模拟 collect_set
行为?
Is there any way to simulate collect_set
behaviour?
推荐答案
不幸的是 SQLAlchemy 有官方的 聚合 ORDER BY 仅支持 Postgresql.您仍然可以使用 通用的 op()
操作符函数,虽然有点笨拙:
Unfortunately SQLAlchemy has official aggregate ORDER BY support for Postgresql only. You can still express it in other dialects using the generic op()
operator function, though a bit hackishly:
func.group_concat(T1.col2.distinct().op("ORDER BY")(T1.col2))
它会编译成类似的东西
group_concat(DISTINCT t1.col2 ORDER BY t1.col2)
这篇关于SQLAlchemy group_concat 有序值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!