On Apr 27, 2012, at 1:41 PM, Laurence Alexander Hurst wrote: > Hi there sqlalchemy users, > > I've been struggling with this for a few days now and cannot spot where I'm > going wrong and would like a little guidance, please; > > I'm trying to create a column_property on my account class (db and Python > class synopsis below) which sums another column from a related table. The SQL > I think it should be used to do the query would be: 'SELECT sum(value) FROM > transaction_account_postings WHERE account_id = %(param_1)s' (or, if > necessary, with 'INNER JOIN accounts ON > transaction_account_postings.account_id = accounts.id' and 'WHERE accounts.id > = %(param_1)s'). > > All I can get SQLAlchemy to give me is hideous subqueries: > 'SELECT (SELECT sum(transaction_account_postings.value) AS sum_1 > FROM transaction_account_postings > WHERE accounts.id = transaction_account_postings.account_id) AS anon_1 > FROM accounts > WHERE accounts.id = %(param_1)s' > > I am aware I can just create a method-based property (i.e. '@property\ndef > total(self):...' in my account class to do the query but the manual seems to > suggest that column_property should be used in preference to this method. It > feels to me like I should be able to do this with a column_property but I > can't for the life of me figure out how.
Ah, I wonder how the docs are giving that impression - column_property() is often a poor choice, the only reasons one would want to use column_property() are so that the expression is evaluated and placed inline into the main SELECT query when the rest of the object is loaded from the database, and additionally that the value is kept persistent once loaded and in sync with the object's state, that is, when the object is expired the value here is expired also. It also can be used as a filter expression in this role. The great limitation of column_property() is that it can only be emitted as a column expression embedded into the columns clause of a SELECT on the parent table, meaning any access it requires to related tables can only be achieved using correlated subqueries, which is what you're seeing here. And actually the query you're illustrating seems correct, too, I'd think you're getting the right result back at least. If you're on PG I'd be curious what it comes up with for EXPLAIN on that subquery, versus, the join, versus the plain SELECT. The subquery/join versions might have the same execution plan (or maybe not). Even though you're on 0.6, the more recent docs have been organized to be more accurate as to the role and limitations of column_property() which are worth reading at http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#mapper-sql-expressions . The @hybrid functionality discussed there is also available with 0.6 via a simple recipe, though @hybrid isn't appropriate here since you definitely need to hit the database for this value. Since you're using deferred, this indicates you want the SELECT to be emitted on access, not within the main SELECT for account objects. So I'd definitely go with @property here. If you want it to evaluate only once, I'd use a memoized @property that caches the value inside of __dict__. If you need the memoized value to expire when data changes elsewhere, then you might need to use event hooks for that part of it, like the after_flush() event. I've added an example for that here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpiryMemoized (uses 0.7 events at the moment, 0.6 has SessionExtension). > > The relevant portion of my database looks like this (there are a few more > columns on each but I've trimmed it down to only the relevant bits): > account(id, description) > transaction_account_postings(id, account_id, value) > > And my sqlalchemy objects (using declarative): > -----8<-------------------->8----- > class TransactionAccountPosting(Base): > __tablename__ = 'transaction_account_postings' > > id = sa.Column(sa.Integer, primary_key=True) > account_id = sa.Column(sa.Integer, sa.ForeignKey('accounts.id'), > nullable=False) > value = sa.Column(sa.Integer, nullable=False) > > def __init__(self, account, value): > self.account = account > self.value = value > > class Account(Base): > __tablename__ = 'accounts' > > id = sa.Column(sa.Integer, primary_key=True) > description = sa.Column(sa.Text) > > transaction_postings = sao.relation(TransactionAccountPosting, > backref=sao.backref('account')) > > def __init__(self, description): > self.description = description > > Account.total = sao.column_property( > sa.select([sa.func.sum(TransactionAccountPosting.value)], Account.id == > TransactionAccountPosting.account_id), deferred=True > ) > -----8<-------------------->8----- > > If it makes any difference I'm using SQLAlchemy 0.6.3. > > Thanks > Laurence > > -- > 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. > -- 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.
