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 [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
-~----------~----~----~----~------~----~------~--~---
