Hi list, Is there really no easier/nicer way to get a count of items alongside object results than the one described at: http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_joins_subqueries ?
>>> from sqlalchemy.sql import func >>> stmt = session.query(Address.user_id, >>> func.count('*').label('address_count')).group_by(Address.user_id).subquery() >>> for u, count in session.query(User, stmt.c.address_count).\ ... outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): ... print u, count =========== I thought something simpler using a subquery instead of a outer join on a subquery would work... But I couldn't make it work. [I'm no expert at SQL optimization but I'd believe the speed should be similar]. session.query(User, select([func.count('*')], User.id == Address.user_id).as_scalar()) It doesn't seem to correlate correctly: no from clause... I think similar queries work (or at least used to) in column_properties?!?. I had to add the manual correlate clause: session.query(User, select([func.count('*')], User.id == Address.user_id).correlate(users).as_scalar()) but even then it doesn't yield any result through the ORM. The generated query yields the correct results in the DB command-line interface though. So I guess it's the ORM part which cannot load it back correctly. It might be related to the fact that the generated query is: SELECT user.[...], (SELECT count(?) AS count_1 FROM backend_blogpost WHERE backend_blog.user = backend_blogpost.blog_user) AS anon_1 FROM backend_blog, backend_blogpost Notice the unnecessary "backend_blogpost" at the end. Is it a bug, or am I doing something wrong/missing something to get the query right? On a related note, I'm dreaming that something like this would be supported: >>> for u, count in session.query(User, User.addresses.count()): ... print u, count -- Gaƫtan de Menten http://openhex.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---