SQLAlchemy 按函数结果排序 [英] SQLAlchemy order by function result
问题描述
这是我拥有的代码并且它正在运行(返回按难度排序的所有问题):
This is the code I have and it is working (returns all problems ordered by difficulty):
def get_noteworthy_problems(self):
ACategory = aliased(Category)
AProblem = aliased(Problem)
all_prob = DBSession.query(AProblem).filter(
AProblem.parent_id == ACategory.id,
ACategory.parent_id == self.id)
noteworthy_problems = \
sorted(all_prob, key=lambda x: x.difficulty(), reverse=True)
return noteworthy_problems
但我想我必须优化这段代码.是否有可能更改具有 order_by
和我的函数 difficulty()
的代码?我的函数返回一个数字.我试过类似的东西:
But I think I must optimize this code.
Is there a possibility to change the code having order_by
and my function difficulty()
? My function returns a number. I tried something like:
result = DBSession.query(AProblem).filter(
AProblem.parent_id == ACategory.id,
ACategory.parent_id == self.id).order_by(
AProblem.difficulty().desc())
但我收到错误 TypeError: 'NoneType' object is not callable
.
推荐答案
混合属性是作为 Python 属性和 SQL 表达式的特殊方法.只要你的难度
函数可以用SQL表达,就可以像普通列一样进行过滤排序.
Hybrid attributes are special methods that act as both a Python property and a SQL expression. As long as your difficulty
function can be expressed in SQL, it can be used to filter and order like a normal column.
例如,如果您将难度计算为问题的鹦鹉数量,如果问题超过 30 天,则乘以 10,您可以使用:
For example, if you calculate difficulty as the number of parrots a problem has, times ten if the problem is older than 30 days, you would use:
from datetime import datetime, timedelta
from sqlalchemy import Column, Integer, DateTime, case
from sqlalchemy.ext.hybrid import hybrid_property
class Problem(Base):
parrots = Column(Integer, nullable=False, default=1)
created = Column(DateTime, nullable=False, default=datetime.utcnow)
@hybrid_property
def difficulty(self):
# this getter is used when accessing the property of an instance
if self.created <= (datetime.utcnow() - timedelta(30)):
return self.parrots * 10
return self.parrots
@difficulty.expression
def difficulty(cls):
# this expression is used when querying the model
return case(
[(cls.created <= (datetime.utcnow() - timedelta(30)), cls.parrots * 10)],
else_=cls.parrots
)
并使用以下命令查询:
session.query(Problem).order_by(Problem.difficulty.desc())
这篇关于SQLAlchemy 按函数结果排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!