使用SQLalchemy在Select语句中以列为列生成SQL

Question

是否有一种方法使SQLalchemy生成一个与当前行相关的子查询的自定义列的查询：

SELECT
 tab1.id,
 tab1.col1, 
 ...,
 (
     SELECT count(1) FROM tab2 
     WHERE tab2.tab1_id = tab1.id
     GROUP BY tab2.col1
 ) as cnt
FROM tab1
WHERE ...
LIMIT 100

使用ORM API？

session.query(Tab1, ?(subquery for additional column)?).filter(...).limit(100)

我正在使用postgresql 9.3和旧版本的sqlalchemy 0.9.8

Answer 1

如果经常需要，并且/或计数是Tab1模型不可或缺的一部分，则应使用其他答案中所述的混合属性。另一方面，如果您仅用于一个查询，则可以使用Query.label()或Query.as_scalar()：

创建标量子查询

count_stmt = session.query(func.count(1)).
    filter(Tab2.tab1_id == Tab1.id).
    group_by(Tab2.col1).
    label('cnt')
session.query(Tab1, count_stmt).filter(...).limit(100)

该子查询将自动将其从封闭查询中所能提供的内容。

Answer 2

您可以做到这一点，但是它与您的书写方式完全不同。您可以创建TAB1的属性，该属性取决于与TAB2的关系(假设tab2.tab1_id是外键，应该是。

您的模型看起来像这样：

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

根据关系的文档

然后您可以添加

之类的东西

@hybrid_property
def number_of_children(self):
    if self.children:
        return len(self.children)
    return 0
@number_of_children.expression
def number_of_children(cls):
    return (select([func.count(Child.id)])
            .where(Child.cover_id == cls.id))

根据此答案和更多文档。

完成此操作后，您可以在此属性上过滤与任何其他基于列的元素相同。