Isolating subquery from its parent

Question

I have a column_property on my model that is a count of the relationships on a secondary model.

membership_total = column_property(
        select([func.count(MembershipModel.id)]).where(
            MembershipModel.account_id == id).correlate_except(None))

This works fine until I try to join the membership model.

query = AccountModel.query.join(MembershipModel)
# ProgrammingError: subquery uses ungrouped column "membership.account_id" from outer query

I can fix this issue by appending:

query = query.group_by(MembershipModel.account_id, AccountModel.id)
# resolves the issue

But I don't really want to do that. I want it to be its own island that ignores whatever the query is doing and just focuses on returning a count of memberships for that particular row's account ID.

What can I do to the column_property to make it more robust and less reliant on what the parent query is doing?

Answer 1

Pass MembershipModel to correlate_except() instead of None, as described here in the documentation. Your current method allows omitting everything from the subquery's FROM-clause, if it can be correlated to the enclosing query. When you join MembershipModel it becomes available in the enclosing query.

Here's a simplified example. Given 2 models A and B:

In [2]: class A(Base):
   ...:     __tablename__ = 'a'
   ...:     id = Column(Integer, primary_key=True, autoincrement=True)
   ...:     

In [3]: class B(Base):
   ...:     __tablename__ = 'b'
   ...:     id = Column(Integer, primary_key=True, autoincrement=True)
   ...:     a_id = Column(Integer, ForeignKey('a.id'))
   ...:     a = relationship('A', backref='bs')

and 2 column_property definitions on A:

In [10]: A.b_count = column_property(
    select([func.count(B.id)]).where(B.a_id == A.id).correlate_except(B))

In [11]: A.b_count_wrong = column_property(
    select([func.count(B.id)]).where(B.a_id == A.id).correlate_except(None))

If we query just A, everything's fine:

In [12]: print(session.query(A))
SELECT a.id AS a_id, (SELECT count(b.id) AS count_1 
FROM b 
WHERE b.a_id = a.id) AS anon_1, (SELECT count(b.id) AS count_2 
FROM b 
WHERE b.a_id = a.id) AS anon_2 
FROM a

But if we join B, the second property incorrectly correlates B from the enclosing query and completely omits the FROM-clause:

In [13]: print(session.query(A).join(B))
SELECT a.id AS a_id, (SELECT count(b.id) AS count_1 
FROM b 
WHERE b.a_id = a.id) AS anon_1, (SELECT count(b.id) AS count_2 
WHERE b.a_id = a.id) AS anon_2 
FROM a JOIN b ON a.id = b.a_id