I forgot to mention, I am using SA 0.3.10.
Thanks,
Allen
On Dec 7, 2007 7:49 AM, Allen Bierbaum <[EMAIL PROTECTED]> wrote:
> 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 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
-~----------~----~----~----~------~----~------~--~---
