I am trying to create two queries with some of my SA ORM objects that
will use the sum of a field found through a relationship. To be a bit
more concrete, here is a simple setup similar to mine.
# table object
users_table = Table('users', meta,
Column('user_id', Integer, primary_key=True),
Column('user_name', String(16)),
Column('state', String(2))
)
order_table = Table('orders', metadata,
Column('user_id', Integer, ForeignKey("users.user_id")),
Column('total', Integer),
)
mapper(Order, order_table)
mapper(User, users_table, properties = {
'orders' : relation(Order)})
What I want to do is two different queries.
1. Return a list of all User objects meeting a given criteria along
with a field that is a sum of all their order totals.
For example:
users = session.query(User).filter(User.c.state == 'IA').XXXXX.all()
for u in users:
print u.rolled_up_order_total
2. Find all users in a given state (User.c.state == "NY") with their
sum of all order totals being greater then some number X.
Very similar to above, but I don't need the total to come out as an
attribute on the object.
Is there some way to do this with SA and the ORM? I know that with
straight up SQL I could create a query for this information and store
the results of an aggregate sum function in a column alias, but how do
I do this with the ORM? Is there a way to create a query that
dynamically injects additional attributes into the loaded object on
demand? (if not, that sounds like a pretty nice feature to me :)
Thanks,
Allen
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
