On Fri, Nov 27, 2009 at 10:55 AM, Oliver Beattie <[email protected]>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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
