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.horne...@gmail.com wrote:
> 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?
>
> --
> 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+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
