Create sql with subquery as column in select statement using SQLAlchemy
Is there a way to get SQLAlchemy to generate a query with a custom column that is a subquery that correlates with the 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 ORM API?
session.query(Tab1, ?(subquery for additional column)?).filter(...).limit(100)
I am using PostgreSQL 9.3 and old SQLAlchemy 0.9.8
If you need it often and / or counting is an integral part of your model Tab1
, you should use a hybrid property like the one described in another answer. If, on the other hand, you only need this for one query, then you can simply create a scalar subquery using Query.label()
either 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 match what it can do from the attached query.
You can do it, but it works very differently the way you wrote it. You can create a Tab1 property that depends on the relationship to tab2 (assuming which tab2.tab1_id
is a foreign key as 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'))
according to relationship documents
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))
into the parent model, according to this answer and more docs .
Once you've done that, you can filter this property just like any other based on the column.