CODEWITHSUNDEEP
pythonpostgresqlsqlalchemycorrelated-subquery
Roman Rader
Roman Rader

Reputation: 1236

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

Upvotes: 20

Views: 28018

Answers (3)

loup
loup

Reputation: 8555

For SQLAlchemy 2 users

Simply define a select statement for your sub-query statement. You can then use it as a column in another select statement.

Using Ilja Everilä's example:

count_stmt = (select(func.count(1))
              .where(Tab2.tab1_id == Tab1.id)
              .group_by(Tab2.col1)
              .scalar_subquery()
              .label('cnt'))

More details are in the SQLAlchemy 2.0 documentation.

Upvotes: 2

Ilja Everilä
Ilja Everilä

Reputation: 52939

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.

Upvotes: 21

jwg
jwg

Reputation: 5837

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.

Upvotes: 3

Related Questions