sqlalchemy, hybrid property case statement

Question

This is the query I'm trying to produce through 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

Following sqlalchemy's documentation, I tried to achieve this using hybrid_property. This is what I have so far, and I'm not getting the right statement. It is not generating the case statement properly.

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

The hybrid property must contain two parts for non-trivial expressions: a Python getter and a SQL expression. In this case, the Python side will be an if statement and the SQL side will be a 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)