This is one of those things that I have to look up every time I go to do it after being away from SA for a while.
You have a table A, with a many-to-one table B referencing A. You want to get summary data from B joined with your table A. The query to retrieve your results in B looks like this:
b_query = DBSession.query(
func.sum( B.some_data_value ).label( 'total' ),
func.sum( 1 ).label( 'count' ),
There's a .subquery() method on your query above, which seems like exactly what you want. It returns a thing that can be run as a (named) sub-select. Here's the piece that always makes me go "hrmmmm". How do you actually join it? You can't reference b_query.id or b_query.total when doing the join, as the Alias (subquery) object doesn't have those attributes. The key is that you need to use the old-style .c attribute of the query when doing joins... like so:
compound_query = DBSession.query(
(b_query,b_query.c.id == A.id)
The other thing that I tend to forget is the need to wrap the join condition into a tuple, but by the time I get to that error I'm already dimly remembering this recipe.
It may be that I'm missing something in SA that makes this easier, but this does appear to work. Hope it helps someone (maybe even me) someday.
Pingbacks are closed.