Functions on results of subquery question

[Oliver Beattie]

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

[Mike Conley]

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