[大数据]SQLAlchemy ORDER BY FIELD()

如下：

query = query.order_by(
? ? ? ? ? ? func.field(VpFaces.sex_id, 2, 1),
? ? ? ? ? ? VpFaces.region_id.asc(),
? ? ? ? ? ? VpFaces.age_id.asc(),
? ? ? ? ? ? VpFaces.id.asc()
? ? ? ? )

按字段对SQLAlchemy ORM对象进行排序，但使用特定的值顺序（既不升序也不降序）。如果我在MySQL上做这个查询，它看起来像

SELECT letter FROM alphabet_table WHERE letter in ('g','a','c','k')
ORDER BY FIELDS( letter, 'g','a','c','k');

sqlalchemy：

session.query(AlphabetTable).filter(
    AlphabetTable.letter.in_(('g','a','c','k')))
.order_by(
    AlphabetTable.letter.in_(('g','a','c','k'))
)

# Answer 1
This may not be a very satisfying solution, but how about using a case expression instead of order by fields:

sqlalchemy.orm.Query(AlphabetTable) \
? ? .filter(AlphabetTable.letter.in_("gack")) \
? ? .order_by(sqlalchemy.sql.expression.case(((AlphabetTable.letter == "g", 1),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (AlphabetTable.letter == "a", 2),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (AlphabetTable.letter == "c", 3),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (AlphabetTable.letter == "k", 4))))

# Answer 2
A sqlalchemy func expression can be used to generate the order by field clause:

session.query(AlphabetTable) \
? ? .filter(AlphabetTable.letter.in_("gack")) \
? ? .order_by(sqlalchemy.func.field(AlphabetTable.letter, *"gack"))

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖