Hey
Thanks for the answers. I have a few follow-ups if you don't mind.
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()
>
I finally managed the case doing this.
@hybrid_property
def balance(self):
return db.session.query(AccountEntry.account_id,
func.sum(AccountEntry.amount).label('b')).group_by(AccountEntry.account_id).having(AccountEntry.account_id==
self.id).one().b
@balance.expression
def balance(cls):
return
Session().query(func.sum(AccountEntry.amount)).group_by(AccountEntry.account_id).having(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...)
>
Well we're just trying out what SQLA is capable of at the moment and trying
to build up a proper api. We run into a similar issue then:
We have an entity "Party" which has n "Language" through "PartyLanguage". On
"PartyLanguage" there is a field called "order". The one with the lowest
"order" is the parties primary language. Now it would be very pretty to be
able to do those things:
1. someparty.primary_language = Language('en')
2. assert someparty.primary_language == Language('en')
3. someparty.languages.append(Language('de'))
4. session.query(Party).filter(Party.primary_language=Language('en')).all()
don't you think so? But we enquire a whole lot of problems. We managed to do
most things except for 4. We implemented @primary_language.expression like
this:
@primary_language.expression
def _primary_language_expression(cls):
return Session().query(Language.iso)\
.join(PartyLanguage)\
.order_by(PartyLanguage.order)\
.group_by(PartyLanguage.party_id, Language.iso,
PartyLanguage.order)\
.having(PartyLanguage.party_id==cls.id)\
.limit(1)\
.as_scalar()
This works so far but there is still the need to use it in a query like
this:
language = Language('en')
session.query(Party).filter(Party.primary_language=language.iso).all()
I guess it's all about the compare behavior of the expression returned
by _primary_language_expression. It includes "Language" as a type into the
query, which leads to a "not supported type" error.
So are we doing it all wrong, is SQLA not ment to write those kind of API's?
Is it possible to achieve what we like to do?
Best regards,
Florian
--
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.
