python - sqlalchemy，混合属性案例语句

Question

这是我试图通过 sqlalchemy 生成的查询

SELECT "order".id AS "id", 
"order".created_at AS "created_at", 
"order".updated_at AS "updated_at", 
CASE 
WHEN box.order_id IS NULL THEN "special" 
ELSE "regular" AS "type"
FROM "order" LEFT OUTER JOIN box ON "order".id = box.order_id

根据 sqlalchemy 的文档，我尝试使用 hybrid_property 来实现这一点。这是我到目前为止所拥有的，我没有得到正确的陈述。它没有正确生成 case 语句。

from sqlalchemy import (Integer, String, DateTime, ForeignKey, select, Column, create_engine)
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()
class Order(Base):
    __tablename__ = 'order'
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime)
    updated_at = Column(DateTime)
    order_type = relationship("Box", backref='order')
    @hybrid_property
    def type(self):
        if not self.order_type:
            return 'regular'
        else:
            return 'special'

class Box(Base):
    __tablename__ = 'box'
    id = Column(Integer, primary_key=True)
    monthly_id = Column(Integer)
    order_id = Column(Integer, ForeignKey('order.id'))

stmt = select([Order.id, Order.created_at, Order.updated_at, Order.type]).\
    select_from(Order.__table__.outerjoin(Box.__table__))
print(str(stmt))

Answer 1

混合属性必须包含非平凡表达式的两部分:Python getter 和 SQL 表达式。在这种情况下，Python 端将是一个 if 语句，而 SQL 端将是一个 case expression。 .

from sqlalchemy import case
from sqlalchemy.ext.hybrid import hybrid_property


@hybrid_property
def type(self):
    return 'special' if self.order_type else 'regular'

@type.expression
def type(cls):
    return case({True: 'special', False: 'regular'}, cls.order_type)