SQLAlchemy orm join against subquery

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(
B.id,
func.sum( B.some_data_value ).label( 'total' ),
func.sum( 1 ).label( 'count' ),
).group_by(
B.id
)

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(
A,
b_query.c.total,
b_query.c.count,
).join(
(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.

Comments

  1. Tobu

    Tobu on 01/24/2010 5:27 p.m. #

    It did help. Though I'm still tempted to use raw SQL. Thanks, serendipity!

  2. john smallberries

    john smallberries on 05/18/2012 6:53 p.m. #

    To make this work, I needed to append .subquery() to b_query, so that it gets an alias in the generated sql. (You might extend your example with a print b_query and print compound_query, to show the generated sql).

Comments are closed.

Pingbacks

Pingbacks are closed.