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.
