On Fri, Nov 27, 2009 at 10:55 AM, Oliver Beattie <oli...@obeattie.com>wrote:
> Hey there, > > I'm probably missing something here, but no matter what I try, I can't > seem to find a way to translate this query into SQLAlchemy code: > > SELECT AVG(sub.average) > FROM ( > SELECT AVG(feedback.overall_rating) AS average > FROM feedback > INNER JOIN listings ON feedback.listing_id = listings.id > WHERE feedback.is_for_driver = false > GROUP BY feedback.listing_id > ) AS sub; > > So, is there any way someone could possibly point me in the right > direction? All of the tables have SA mappers defined for them (named > Feedback and Listing), if that helps. I've tried to do things like: > sa.select([sa.func.avg('sub.average'), sa.select([sa.func.avg > (Feedback.overall_rating).label('average')]).alias('sub')]) > but no avail (and I know that doesn't include the grouping or the > where :) > > Anyway, if someone could possibly help me out, I'd be most grateful. > > Thanks, > Oliver Beattie > How about this? sub = session.query(func.avg(Feedback.overall_rating).label('average')).\ join(Listings).filter(Feedback.is_for_driver==False).\ group_by(Feedback.listing_id).subquery() qry = session.query(func.avg(sub.c.average)) print qry In general: construct a query that gives desired result for subquery, labeling columns as needed make it a subquery() refer to columns of the subquery using "subq.c.column" in final query It took me a while to get used to this because using the ".c." with ORM didn't feel natural. -- Mike Conley -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.