On Nov 10, 2009, at 4:24 PM, hb wrote:
>
> Hi all,
>
> I have two models: User and Project. The relationship is one-to-many.
> What I want to achieve is querying users and the number of projects
> they are connected to. I also want to order the users according to the
> project counts.
>
> The following code seems to work, but the result is a list of (User,
> long) tuple.
>
> users = Session.query(User).select_from(
> outerjoin(User, Project, Project.user_id == User.id)) \
> .add_column("count(projects.id)") \
> .group_by(User.id) \
> .order_by("count(projects.id) desc") \
> .all()
>
> I want to access the project count as an attribute of user object. So
> the result will be just list
> of User objects, not a 2-tuple.
>
> I searched the documentation with keywords like "dynamic attributes"
> or "adding extra attributes
> to model" but unfortunately, I did not find a way.
>
> Is it a doable?
you would normally add a mapped attribute to User representing that count. An
example of specifically parent + count of child objects is at
http://www.sqlalchemy.org/docs/05/mappers.html#sql-expressions-as-mapped-attributes
.
If you use "deferred()" instead of "column_property()", the attribute will not
load within query() unless you add .options(undefer('my_count')) to the query,
which provides an effective way to turn on the attribute selectively for a
particular load. the attribute will always load when accessed if it was not
already.
otherwise you might try querying for the columns you need, like query(User.id,
User.name, func.count(Projects.id).label('project_count')).... which will
return a single named tuple that will have "id", "name" and "project_count" as
attributes.
you can also just run through your result and attach each "count" to each
"User" object. though you dilute what it means to be a "User".
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
