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
source to share
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.
source to share
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.
source to share