Subquery with count in SQLAlchemy
<p>Given these SQLAlchemy model definitions:</p>

<pre><code>class Store(db.Model):
    __tablename__ = 'store'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)


class CustomerAccount(db.Model, AccountMixin):
    __tablename__ = 'customer_account'

    id = Column(Integer, primary_key=True)
    plan_id = Column(Integer, ForeignKey('plan.id'), index=True, nullable=False)

    store = relationship('Store', backref='account', uselist=False)
    plan = relationship('Plan', backref='accounts', uselist=False)


class Plan(db.Model):
    __tablename__ = 'plan'

    id = Column(Integer, primary_key=True)
    store_id = Column(Integer, ForeignKey('store.id'), index=True)
    name = Column(String, nullable=False)
    subscription_amount = Column(Numeric, nullable=False)
    num_of_payments = Column(Integer, nullable=False)
    store = relationship('Store', backref='plans')
</code></pre>

<p>How do I write a query to get a breakdown of subscription revenues by plan?
I'd like to get back a list of the plans for a given Store, and for each plan the total revenues for that plan, calculated by multiplying Plan.subscription_amount * Plan.num_of_payments * num of customers subscribed to that plan</p>

<p>At the moment I'm trying with this query and subquery:</p>

<pre><code>store = db.session.query(Store).get(1)

subscriber_counts = db.session.query(func.count(CustomerAccount.id)).as_scalar()

q = db.session.query(CustomerAccount.plan_id, func.sum(subscriber_counts * Plan.subscription_amount * Plan.num_of_payments))\
  .outerjoin(Plan)\
  .group_by(CustomerAccount.plan_id)
</code></pre>

<p>The problem is the subquery is not filtering on the current plan id.</p>

<p>I also tried with this other approach (no subquery):</p>

<pre><code>q = db.session.query(CustomerAccount.plan_id, func.count(CustomerAccount.plan_id) * Plan.subscription_amount * Plan.num_of_payments)\
    .outerjoin(Plan)\
    .group_by(CustomerAccount.plan_id, Plan.subscription_amount, Plan.num_of_payments)
</code></pre>

<p>And while the results seem fine, I don't know how to get back the plan name or other plan columns, as I'd need to add them to the group by (and that changes the results).</p>

<p>Ideally if a plan doesn't have any subscribers, I'd like it to be returned with a total amount of zero.</p>

<p>Thanks!</p>

<p>Thanks to Alex GrÃ¶nholm on #sqlalchemy I ended up with this working solution:</p>

<pre><code>from sqlalchemy.sql.expression import label
from sqlalchemy.sql.functions import coalesce

from instalment.models import db
from sqlalchemy import func, desc


def projected_total_money_volume_breakdown(store):
    subscriber_counts = db.session.query(CustomerAccount.plan_id, func.count(CustomerAccount.id).label('count'))\
        .group_by(CustomerAccount.plan_id)\
        .subquery()

    total_amount_exp = coalesce(subscriber_counts.c.count, 0) * Plan.subscription_amount * Plan.num_of_payments

    return db.session.query(Plan, label('total_amount', total_amount_exp))\
        .outerjoin(subscriber_counts, subscriber_counts.c.plan_id == Plan.id)\
        .filter(Plan.store == store)\
        .order_by(desc('total_amount'))\
        .all()
</code></pre>

python sql sqlalchemy