SQLAlchemy 实现VALUES内联表与聚合查询JOIN关联

你遇到的sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target错误来自两处错误写法：

• 你将已经调用.subquery()生成的合法可连接selectable对象，额外套了一层self.session.query(inline_enum_table)，得到的是ORM的Query对象，不属于SQLAlchemy JOIN方法支持的表、映射实体、子查询类可连接目标。
• 你尝试在Query对象上调用.label()方法，label()仅用于给列/表达式起别名，不能作用于Query对象，子查询的别名直接在.subquery()方法传入别名参数即可。

构造Values内联表时直接生成可连接的子查询对象，不要额外套session.query层，关联时直接传入子查询作为JOIN目标，同时注意将需要返回的内联表字段加入查询实体、加入分组字段即可：

from sqlalchemy import Values, column, String, Integer, case, and_, count

# 构造内联枚举表，直接转为带别名的可连接子查询
inline_enum_table = (
    Values(
        column('key', String),
        column('value', String),
        column('ordering', Integer)
    )
    .data([(e.name, e.value, i) for i, e in enumerate(DurationType)])
    .subquery("duration_enum")
)

# 定义时长分桶表达式
duration_bucket = case(
    (self.model.duration < 60, DurationType.LESS_THAN_1.name),
    (and_(60 <= self.model.duration, self.model.duration < 60 * 5), DurationType.FROM_1_TO_5.name),
    (and_(60 * 5 <= self.model.duration, self.model.duration < 60 * 10), DurationType.FROM_5_TO_10.name),
    (and_(60 * 10 <= self.model.duration, self.model.duration < 60 * 20), DurationType.FROM_10_TO_20.name),
    (and_(60 * 20 <= self.model.duration, self.model.duration < 60 * 30), DurationType.FROM_20_TO_30.name),
    (60 * 30 <= self.model.duration, DurationType.MORE_THAN_30.name)
).label('id')

# 组装关联查询
result = (
    self.session.query(self.model)
    .filter(self.model.duration != None)
    .with_entities(
        duration_bucket,
        inline_enum_table.c.value,
        inline_enum_table.c.ordering,
        count().label("stat_count")
    )
    .join(inline_enum_table, inline_enum_table.c.key == duration_bucket)
    .group_by(duration_bucket, inline_enum_table.c.value, inline_enum_table.c.ordering)
    .having(count() > 0)
    .order_by(inline_enum_table.c.ordering)
)

• 子查询的字段需要通过.c.字段名的形式访问，比如inline_enum_table.c.key对应内联表的key字段。
• 如果需要保留无统计数据的分桶（统计值为0的分桶也返回），将join替换为outerjoin，调整关联顺序以内联枚举表为左表关联聚合结果即可。

内容的提问来源于stack exchange，提问作者go2nirvana