Aggregate function expression is put in the `FROM` clause

Question

I'm trying to build a query into a model (Loan) filtered by some criteria and joined into another model (LoanPayment).

The first model has a one-to-many relationship with the second, and I'd like to collect an aggregate value over a column of the second model.

The models are defined like this:

class Loan(Base):
    __tablename__ = 'loan'
    id = Column(Integer, primary_key=True)
    granted_date = Column(Date, nullable=False)
    state = Column(String(50), default='GRANTED', index=True)
    value = Column(Numeric(19, 10, asdecimal=True),
                   nullable=False, default=money.Decimal(0))
    [... other columns ...]

class LoanPayout(Base):
    __tablename__ = 'loan_payout'
    id = Column(Integer, primary_key=True)
    payment_date = Column(Date, nullable=False)
    value = Column(Numeric(19, 10, asdecimal=True),
                   nullable=False, default=money.Decimal(0))
    loan_id = Column(Integer, ForeignKey('loan.id'),
                     nullable=False, index=True)
    loan = relationship('Loan',
                        backref=backref('payouts', lazy='dynamic',),
                        foreign_keys=[loan_id], order_by=payment_date.asc)
    [... other columns ...]

I'd like to query all Loan.granted_date records, filtering according to a certain criteria (say state='LATE'), and also return the sum() of values of all related payouts, grouped by loan.

I tried the following orm query:

session.query(
    Loan.id,
    Loan.granted_date,
    func.sum(LoanPayout.principal).alias('loan_payout_total'),
).filter(
    Loan.state == 'LATE',
).join(
    LoanPayout
).group_by(Loan.id)

But SQLAlchemy (version 1.2.13) is rendering the following, which is not even valid SQL:

SELECT
    loan.id AS loan_id,
    loan.sale_date AS loan_sale_date,
    loan_payout_total.sum_1 AS loan_payout_total_sum_1
FROM
    sum(loan_payout.principal) AS loan_payout_total,
    loan
JOIN loan_payout
    ON loan.id = loan_payout.loan_id
WHERE
    loan.state = %(state_1)s
GROUP BY
    loan.id

The query is almost what I expect: it joins correctly the two tables, renders the sum() column correctly and groups by the expected column.

But it puts what should have been the sum() column with its alias in the FROM section instead of the SELECT section:

FROM
    sum(loan_payout.principal) AS loan_payout_total,
    loan

And then it puts an aliased column supposedly coming from that weird "selectable" into the SELECT clause:

    loan_payout_total.sum_1 AS loan_payout_total_sum_1

How do I get SQLAlchemy to render this query correctly, using the ORM support?

Related: https://stackoverflow.com/questions/9187530/using-alias-for-select-as-in-sqlalchemy — Ilja Everilä, Nov 09 '18 at 16:16

Ilja Everilä · Accepted Answer · 2018-11-09T16:23:51.127

1

Instead of

func.sum(LoanPayout.principal).alias('loan_payout_total')

you want a label:

func.sum(LoanPayout.principal).label('loan_payout_total')

FunctionElement.alias() produces an aliased FROM-item, which is why it is put in the FROM clause. It's valid in for example Postgresql.

edited Nov 09 '18 at 16:23

answered Nov 09 '18 at 16:11

Ilja Everilä

50,538
7
126
127

Thank you. I can understand why both `.label()` and `.alias()` are available on `func.xxx()`, but I can't, for the life of me, understand why they wouldn't just make it generate the correct code depending on the context (`FROM` vs `SELECT`) – LeoRochael Nov 09 '18 at 19:55
It actually has a logic to it that you'll run into using SQLA. Passing selectable things (tables, models, aliases, CTEs) as entities to query building constructs automatically add them to the `FROM` clause. – Ilja Everilä Nov 09 '18 at 20:03
Also, personally I'd find it a lot more confusing, if the produced alias construct acted entirely differently in different contexts. Everywhere in SQLA `alias()` is used to produce "table" aliases, while `label()` names column expressions. – Ilja Everilä Nov 09 '18 at 20:14

Aggregate function expression is put in the `FROM` clause

1 Answers1