On 3.8.2015 17:24, jurie.horne...@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? > > -- > 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.
