我有一个 hybrid_property，它根据对一对多关系进行的一些计算返回一个字符串。
hybrid_property 表达式的原始 sql 是： 这是原始 sql：
SELECT
CASE
WHEN s.quantity_received = 0 THEN "unreceived"
WHEN s.dif = 0.0 THEN "received"
WHEN s.dif > 0.0 THEN "partially_received"
WHEN s.dif < 0.0 THEN "over_received"
END as status
FROM (
SELECT li.quantity_received, sum(li.quantity - li.received) as 'dif'
FROM line_items as li
WHERE li.o_id = xxx
) as s
模型
class LineItem(BaseModel):
__table__ = Table('line_items', autoload=True)
order = relationship("Order", backreef="line_itms", primaryjoin="Order.id == foregin(LineItem.o_id)")
class Order(BaseModel):
__table__ = Table('orders', autoload=True)
@hybrid_property
def status(self):
qty_received, qty_ordered = 0, 0
for li in self.line_items:
if li.status != "cancelled":
qty_ordered += li.quantity
qty_received += li.quantity_received
if qty_received == 0:
status = "unreceived"
elif qty_received == qty_ordered:
status = "received"
elif qty_received < qty_ordered:
status = "partially_received"
elif qty_received > qty_ordered:
status = "over_received"
return status
@status.expression
def status(cls):
line_items_calc = select([LineItem.quantity_received,
func.sum(LineItem.quantity - LineItem.quantity_received).label('dif')]) \
.where(and_(LineItem.o_id == Order.id,
or_(LineItem.fulfillment_status != "cancelled",
LineItem.fulfillment_status == None))) \
.alias()
qq = select([
case([
(qs.c.quantity_received == 0, "unreceived"),
(qs.c.dif == 0, "received"),
(qs.c.dif > 0, "partially_received"),
(qs.c.dif < 0, "over_received")]
)]) \
.select_from(line_items_calc) \
.as_scalar()
return qq
我有 2 个订单，o1 和 o2 带有订单项：
LineItem(o_id=o1.id, quantity=1, quantity_received=1)
LineItem(o_id=o2.id, quantity=1, quantity_received=0)
LineItem(o_id=o2.id, quantity=2, quantity_received=1)
Order1 的状态应该是“received”，Order2 的状态应该是“partially_received”。
但是当我查询“received”时我什么也没得到，当查询“partially_received”时我得到 2 个结果而不是一个。
看起来它没有按 Order.id 过滤 LineItems，因此它使用 all 来计算状态（因为 total_qty 为 4，收到的总数为 2，这将给出“partially_received”）
Order.query().filter(Order.status == 'received').all() # returns []
Order.query().filter(Order.status == 'partially_received').all() # returns [Order1, Order2]
如果添加.correlate_except(LineItem)到line_items_calc查询，我会收到以下错误：
OperationalError: (_mysql_exceptions.OperationalError) (1054, "Unknown column 'orders.id' in 'where clause'") [SQL: u'SELECT count(*) AS count_1 \nFROM (SELECT * \nFROM orders \nWHERE orders.account_id) = %s AND (SELECT CASE WHEN (a_3.quantity_received = %s) THEN %s WHEN (a_3.dif = %s) THEN %s WHEN (a_3.dif > %s) THEN %s WHEN (a_3.dif < % s) THEN %s END AS a_2 \nFROM (SELECT line_items.quantity_received ASquantity_received, sum(line_items.quantity - line_items.quantity_received) AS dif \nFROM line_items \nWHERE line_items.o_id = orders.id AND (line_status.fill) s OR line_items.fulfillment_status IS NULL)) AS a_3) = %s) AS a_1'] [参数：(1L, 0, 'unreceived', 0, 'received', 0, 'partially_received', 0, 'over_received', '取消'，你'over_received')]
您似乎试图将表达式与最外层查询相关联，但事实证明，当前的嵌套子查询方法在 MySQL 中不可行，因为与其他一些相比，它根本不允许在 FROM 子句中使用相关子查询不允许与以前的 FROM 列表项相关联的数据库，除非使用 LATERAL。
另一方面，嵌套子查询是多余的，因为您可以在 SELECT 列表中的 CASE 表达式中使用聚合，但在您当前的子查询中，您可以混合使用非聚合和聚合表达式：
SELECT li.quantity_received, sum(li.quantity - li.received) as 'dif'
这很可能不是您想要的。其他一些数据库甚至不允许执行这样的查询，但li.quantity_received如果禁用ONLY_FULL_GROUP_BY，MySQL 会默默地从组中未指定的行中选择一个值。它在 5.7.5 及更高版本中默认启用，您应该考虑启用它。看看您的混合资产的另一半，您可能还打算计算收到数量的总和。
以下是满足您在问题中提出的 2 个测试用例的状态表达式版本：
@status.expression
def status(cls):
qty_received = func.coalesce(func.sum(LineItem.quantity_received), 0)
qty_ordered = func.coalesce(func.sum(LineItem.quantity), 0)
return select([case([
(qty_received == 0, "unreceived"),
(qty_received == qty_ordered, "received"),
(qty_received < qty_ordered, "partially_received"),
(qty_received > qty_ordered, "over_received")])]).\
where(and_(func.coalesce(LineItem.fulfillment_status, "") != "cancelled",
LineItem.o_id == cls.id)).\
correlate_except(LineItem).\
as_scalar()
我相信它比您的原始方法更接近于 Python 方面的方法。请注意使用 COALESCE 进行 NULL 处理。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句