SqlAlchemy puts SELECT Subquery in FROM clause instead of SELECT

Question

My target SQL is the following, which is valid,

SELECT a.agreement_group_id, 
       (select id from agreement_t where agreement_group_id = a.agreement_group_id and 
                                         active = 'Y'),
       ...
FROM ets.agreement_t a
WHERE requester_uniqueidentifier = '0010079170'
GROUP BY a.agreement_group_id 
ORDER BY a.agreement_group_id

But SqlAlchemy is producing the following -- and complaining that I don't have anon_1 in GROUP BY due to its placement of the sub-select in FROM,

SELECT agreement_t_1.agreement_group_id AS agreement_t_1_agreement_group_id, 
       anon_1.id AS anon_1_id, 
       ...
FROM ets.agreement_t AS agreement_t_1, 
     (SELECT ets.agreement_t.id AS id   
      FROM ets.agreement_t, ets.agreement_t AS agreement_t_1
      WHERE ets.agreement_t.agreement_group_id = agreement_t_1.agreement_group_id AND  
            ets.agreement_t.active = 'Y') AS anon_1
WHERE agreement_t_1.requester_uniqueidentifier = '0010079170' 
GROUP BY agreement_t_1.agreement_group_id, anon_1.id 
ORDER BY agreement_t_1.agreement_group_id

Python SqlAlchemy code:

agreement = aliased(AgreementT)
subqueryActive = db_session.query(AgreementT.id).filter(
                    (AgreementT.agreement_group_id == agreement.agreement_group_id), 
                    (AgreementT.active == 'Y')
                  ).subquery()

result = (db_session.query(
        agreement.agreement_group_id,
        subqueryActive,
        ...
        .filter(*filters)
        .group_by(agreement.agreement_group_id)
        .order_by(agreement.agreement_group_id)
        .all())

I don't need any other Joins. As you can see, the subquery subqueryActive already references the alias agreement which is used in the main query. So why is the Sub-Select not placed properly in the SELECT, but rather in the FROM, with the following error?

psycopg2.errors.GroupingError: column "anon_1.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ent_group_id AS agreement_t_1_agreement_group_id, anon_1.id ...
                                                         ^

`subquery()` produces a `FromObject`, you'll want to use `as_scalar()` / `label()` instead (older SQLA), or was it `scalar_subquery()` in the new versions. — Ilja Everilä, Oct 08 '21 at 17:32
Yes you're exactly right. I found it out at almost the same time as you replied. thanks — gene b., Oct 08 '21 at 18:13

score 0 · Accepted Answer · answered Oct 08 '21 at 18:19

0

If the sub-Select should be part of the SELECT, we can't use .subquery(), we need to use .label() instead.

Example here: https://stackoverflow.com/a/43655840/1005607

Thanks for the tip @Ilja Everilä

answered Oct 08 '21 at 18:19

gene b.

10,512
21
115
227

SqlAlchemy puts SELECT Subquery in FROM clause instead of SELECT

1 Answers1