Thanks Michael, I really appreciate your help.
On Wednesday, 27 May 2015 14:32:48 UTC+1, Michael Bayer wrote: > > > > On 5/27/15 2:43 AM, Nathan wrote: > > Hi, > > I'm trying to convert the following query to sqlalchemy: > > SELECT cg.id, cg.name cg.status, history.action, history.timestampFROM > customergame cgJOIN history ON history.id = (SELECT id FROM history h WHERE cg > .id = h.customergame_id ORDER BY timestamp DESC LIMIT 1)ORDER BY gamename ASC > > > so the first thing to note, the subquery here is used in an ON > expression against the "=" operator. This means we would like it to act > like a column, not a set of rows, and is hence a "scalar subquery" (see > http://docs.sqlalchemy.org/en/rel_1_0/glossary.html#term-subquery) > > > > Essentially to select the customergame entry and the last entry in the > history table for that customer game. > > I've a number of ways to try and archive this, however, nothing so seems > to work. For example: > > subq = db.session.query(History.id).filter(Customergame.id==History. > customergame_id).order_by(History.timestamp).limit(1).subquery() > > > which means, we don't want to use subquery(). We want to use as_scalar() > which has the subquery act like a column. A Core example is here > http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#scalar-selects > but same idea for ORM. > > Another thing to note, the correlation in the FROM clause is a little > non-standard (seems to be accepted by current PG/MySQL versions though), > but SQLAlchemy doesn't expect this to happen normally so we also have to > tell it to correlate that explicitly. Because I could see this part > causing problems I had to verify with a test script (which means you get a > working version for free!). It is attached. > > > > > > db.session.query(Customergame).join(History, History.id==subq.c.id) > > Which produces: > SELECT customergame., > FROM customergame JOIN history ON history.id = anon_1.id > > Which in turn leads to a exception when it executes: > ProgrammingError: (ProgrammingError) missing FROM-clause entry for > table "anon_1" > > > Has anybody got any ideas of how to achieve this type of query? > > Many thanks in advance! > -- > 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 <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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.
