query with HAVING COUNT doesn't work as expected

[jurie.h...@gmail.com]

I am using Python 2.7, SQLAlchemy 0.9.3, and PostgreSQL 9.4.

This:

session.query(self.db.recording_table.c.id).\
    join(self.db.frame_table, self.db.recording_table.c.id == self.db.frame_table.c.recording_id).\
    group_by(self.db.recording_table.c.id).\
    having(func.coalesce(func.count(self.db.frame_table.c.frame_nr), 0) < 2).\
    all()

generates the following SQL:

SELECT recordings.id AS recordings_id
FROM recordings JOIN recording_frames ON recordings.id = recording_frames.recording_id GROUP BY recordings.id
HAVING coalesce(count(recording_frames.frame_nr), %(param_1)s) < %(coalesce_1)s
{'param_1': 0, 'coalesce_1': 2}

and returns an empty list.

If I execute this in pgAdmin:

SELECT recordings.id
FROM recordings
LEFT JOIN recording_frames
ON recordings.id = recording_frames.recording_id
GROUP BY recordings.id
HAVING (coalesce(count(recording_frames.frame_nr), 0) < 2)

I get a list with 2 IDs in the recordings table, which is the expected result.

I have no idea why SQLAlchemy won't give me the same result. If I change the criterion to > 0 I get the same result as with raw SQL. It seems SQLAlchemy, with the code above, somehow filters out the rows where COUNT returns nothing, despite the COALESCE.

What am I doing wrong?

[Ladislav Lenart]

Hello.

pgAdmin query uses LEFT JOIN whereas SQLAlchemy query uses (inner) JOIN. Replace
.join(...) with .outerjoin(...) in your SQLAlchemy query.

HTH,

Ladislav Lenart

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email
> to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[jurie.h...@gmail.com]

Thanks! That solved it.

I'm a JOIN newbie, so I didn't realize left join and outer join were the same thing.

J.

[Ladislav Lenart]

On 3.8.2015 17:24, jurie.h...@gmail.com wrote:
> Thanks! That solved it.
>
> I'm a JOIN newbie, so I didn't realize left join and outer join were the same thing.

Well, in that case this might be of some use to you...

SQL:
* JOIN is short for INNER JOIN.
* LEFT JOIN is short for LEFT OUTER JOIN.
* RIGHT JOIN is short for RIGHT OUTER JOIN.
* FULL JOIN is short for FULL OUTER JOIN.
For a detailed explanation see e.g.:
http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html

SQLAlchemy ORM supports only:
* INNER JOIN with join().
* LEFT OUTER JOIN with outerjoin().

You can simulate RIGHT OUTER JOIN with LEFT JOIN if you change the order of the
tables in the query.

HTH,

Ladislav Lenart

> J.
>
>
>
> On Monday, August 3, 2015 at 5:08:44 PM UTC+2, Ladislav Lenart wrote:
>
> Hello.
>
> pgAdmin query uses LEFT JOIN whereas SQLAlchemy query uses (inner) JOIN.
> Replace
> .join(...) with .outerjoin(...) in your SQLAlchemy query.
>
> HTH,
>
> Ladislav Lenart
>
>
> On 3.8.2015 16:48, jurie.h...@gmail.com <javascript:> wrote:
> > I am using Python 2.7, SQLAlchemy 0.9.3, and PostgreSQL 9.4.
> >
> > This:
> >
> > session.query(self.db.recording_table.c.id

> <http://self.db.recording_table.c.id>).\
> > join(self.db.frame_table, self.db.recording_table.c.id
> <http://self.db.recording_table.c.id> == self.db.frame_table.c.recording_id).\
> > group_by(self.db.recording_table.c.id
> <http://self.db.recording_table.c.id>).\

> > having(func.coalesce(func.count(self.db.frame_table.c.frame_nr), 0) <
> 2).\
> > all()
> >
> >
> > generates the following SQL:
> >

> > SELECT recordings.id <http://recordings.id> AS recordings_id

> > FROM recordings JOIN recording_frames ON recordings.id

> <http://recordings.id> =
> > recording_frames.recording_id GROUP BY recordings.id <http://recordings.id>

> > HAVING coalesce(count(recording_frames.frame_nr), %(param_1)s) <
> %(coalesce_1)s
> > {'param_1': 0, 'coalesce_1': 2}
> >
> > and returns an empty list.
> >
> > If I execute this in pgAdmin:
> >

> > SELECT recordings.id <http://recordings.id>

> > FROM recordings
> > LEFT JOIN recording_frames

> > ON recordings.id <http://recordings.id> = recording_frames.recording_id
> > GROUP BY recordings.id <http://recordings.id>

> > HAVING (coalesce(count(recording_frames.frame_nr), 0) < 2)
> >
> > I get a list with 2 IDs in the recordings table, which is the expected
> result.
> >
> > I have no idea why SQLAlchemy won't give me the same result. If I change the
> > criterion to > 0 I get the same result as with raw SQL. It seems SQLAlchemy,
> > with the code above, somehow filters out the rows where COUNT returns
> nothing,
> > despite the COALESCE.
> >
> > What am I doing wrong?
>