SQLAlchemy ORDER BY FIELD() [英] SQLAlchemy ORDER BY FIELD()
问题描述
我正在尝试按字段对 SQLAlchemy ORM 对象进行排序,但具有特定的值顺序(既不是升序也不是降序).如果我在 MySQL 上做这个查询,它看起来像;
I am trying to sort an SQLAlchemy ORM object by a field, but with a specific order of the values (which is neither ascending or descending). If I was doing this query on MySQL, it would look like;
SELECT letter FROM alphabet_table WHERE letter in ('g','a','c','k')
ORDER BY FIELDS( letter, 'g','a','c','k');
对于输出:
letter
------
g
a
c
k
对于 SQLAlchemy,我一直在尝试以下方法:
For SQLAlchemy, I've been trying things along the lines of:
session.query(AlphabetTable).filter(AlphabetTable.letter.in_(('g','a','c','k'))).order_by(AlphabetTable.letter.in_(('g'),'a','c','k'))))
session.query(AlphabetTable).filter(AlphabetTable.letter.in_(('g','a','c','k'))).order_by(AlphabetTable.letter.in_(('g','a','c','k')))
哪个不起作用...有什么想法吗?这是一个小的一次性常量列表,我可以用订单创建一个表然后加入,但这似乎有点太多了.
Which doesn't work... any ideas? It's a small one-time constant list, and I could just create a table with the order and then join, but that seems like a bit too much.
推荐答案
这可能不是一个非常令人满意的解决方案,但是如何使用 case 表达式 而不是按字段排序
:
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))))
这篇关于SQLAlchemy ORDER BY FIELD()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!