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.
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.
