Generate sql with subquery as a column in select statement using SQLAlchemy

Is there a way to make SQLAlchemy generate a query with a custom column that is a subquery that correlates with current row:

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

using the ORM API?

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

I’m using PostgreSQL 9.3 and old version of SQLAlchemy 0.9.8

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you need this often, and/or the count is an integral part of your Tab1 model, you should use a hybrid property such as described in the other answer. If on the other hand you need this just for a single query, then you could just create the scalar subquery using Query.label(), or 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)

The subquery will automatically correlate what it can from the enclosing query.

Method 2

You can do this, but it works in a quite different way to how you have written it. You can create a property of Tab1 which depends on the relationship to tab2 (assuming that tab2.tab1_id is a foreign key, which it should be.

Your models look like this:

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'))

as per the docs on relationships

Then you can add something like

@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))

to the Parent model, as per this answer and more docs.

Once you’ve done this, you can filter on this property the same as any other column-based one.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0