Re: [sqlalchemy] Selecting more than one entity from a subquery
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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Selecting more than one entity from a subquery
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 sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Selecting more than one entity from a subquery
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 -- 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/-/2pEAnubaBukJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Selecting more than one entity from a subquery
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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
