Selecting more than one entity from a subquery

[Adrian]

I have seen that it is possible to get an entity from a subquery with the aliased(entity,statement) construct. Is there also a way to get more than one entity from a subquery, for example 2?

Cheers

Adrian

[Michael Bayer]

On Sep 16, 2011, at 5:28 AM, Adrian wrote:

> I have seen that it is possible to get an entity from a subquery with the aliased(entity,statement) construct. Is there also a way to get more than one entity from a subquery, for example 2?

You can of course have a subquery that represents multiple entities internally, and then the subquery itself has a .c. attribute which you can use to construct further statements in terms of the columns of the entity. Otherwise it seems like you'd be asking for myalias.Entity1.foo, myalias.Entity2.bar ? If that's what you mean, I think for the moment you'd need to stick with a pattern like myalias.c.entity1_foo, myalias.c.entity2_bar, where "myalias" is a Core "Alias" construct, rather than an ORM alias.

Feel free to send an example of what you're looking for if more info is needed.

[Adrian]

I created a gist with example code https://gist.github.com/1223926

query.sql shows you the basic SQL query of what I am trying to do - fetching the Residue as an entity and the 12 summed values from the subquery. orm-code.py is the orm code for the upper part of the query (the part I am struggling with at the moment). The tricky part for me is how to the select the Residue entity and the result of the aggregates.

[Michael Bayer]

OK well in this scenario, one approach people sometimes do in order to fetch the columns of the entity as well as a bunch of aggregates, is to GROUP BY all the non-aggregated columns, so you'd get rows like:

entity.id entity.attr1 entity.attr2, ..., sum(otherent.attr1), sum(otherent.attr2), ...

the problem with that is that it's pretty poor practice to GROUP by the whole set of columns in the primary entity when you really just need to get the primary key.  MySQL specifically would allow you to put all the columns there without the need to GROUP BY all of them, but I see you're not on Mysql and I wouldn't recommend that error-prone approach either.

IMHO the best way to get at entity rows + aggregates is to select the entity rows, then JOIN to the aggregates within a subquery.  That would look like:

sift = session.query(Residue.id.label('residue_id'), func.sum(...), func.sum(...), func.sum(...)).join(...).filter(...)

sift = sift.subquery()

rows = session.query(Residue, sift).join(sift, sift.c.residue_id==Residue.id)

you'd then get back tuples of (Residue, agg1, agg2, agg3, ...).

On Sep 17, 2011, at 9:31 AM, Adrian wrote:

I created a gist with example code https://gist.github.com/1223926

query.sql shows you the basic SQL query of what I am trying to do - fetching the Residue as an entity and the 12 summed values from the subquery. orm-code.py is the orm code for the upper part of the query (the part I am struggling with at the moment). The tricky part for me is how to the select the Residue entity and the result of the aggregates.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/qoR2Raq9UXgJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.