On Feb 14, 2011, at 4:20 AM, Florian Mueller wrote:
> Hi
>
> I'm confronted with the following situation:
>
> I have two entities:
>
> Account 1:n AccountEntry
>
> AccountEntry has a column (and property) 'amount'.
>
> Now I'd like to implement a @hybrid_property.expression 'balance' on Account
> which uses func.sum(AccountEntry.amount) to calculate the account's balance.
>
> When I do:
>
> @balance.expression
> def balance(cls):
> return func.sum(AccountEntry.amount)
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) (b'ERROR', b'42803',
> b'aggregates not allowed in WHERE clause')
>
> which is ok, since you have to have a sub-select like
>
> select * from accounts as a where (select sum(ae.amount) as balance from
> account_entries as ae where ae.account_id = a.id) > 0
>
> Now my question: How to achieve this using @hybrid_property.expression? Which
> query fragment I have to return?
you can return the subquery:
@balance.expression
def balance(cls):
return
Session().query(func.sum(AccountEntry.amount)).filter(AccountEntry.account_id==cls.id).as_scalar()
note I'm using a do-nothing Session() there since we're generating a SQL
construct that will be embedded into another.
Usually when I work with aggregates like that I like to keep things more
explicit. Suppose your original Account.balance descriptor:
balances = session.query(AccountEntry.account_id,
Account.balance.label('balance')).group_by(AccountEntry.account_id).subquery()
accounts = session.query(Account).join(balances,
Account.entries).filter(balances.c.balance > 0)
i.e. the hybrid represents a particular type of expression, but it is
context-dependent. Its a typical "practicality beats purity" type of decision
(like the rest of SQLA...)
--
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.
