On Fri, Apr 27, 2012 at 02:22:11PM -0400, Michael Bayer wrote: > 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). >
Thanks for the reply, Michael. Having re-read the docs I think my brain somehow twisted "In cases where a SQL query more elaborate than what orm.column_property() or hybrid_property can provide must be emitted, a regular Python function accessed as an attribute can be used, assuming the expression only needs to be available on an already-loaded instance."[0] into "orm.column_property() should be used in preference to functions mapped as attributes", which is clearly not what it says. [0] http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#using-a-plain-descriptor I am indeed using Postgresql, here's the results you asked for: -----8<-------------------->8----- EXPLAIN SELECT (SELECT sum(transaction_account_postings.value) AS sum_1 budget(> FROM transaction_account_postings budget(> WHERE accounts.id = transaction_account_postings.account_id) AS anon_1 budget-> FROM accounts budget-> WHERE accounts.id =1; QUERY PLAN --------------------------------------------------------------------------------------------- Index Scan using accounts_pkey on accounts (cost=0.00..57.51 rows=1 width=4) Index Cond: (id = 1) SubPlan 1 -> Aggregate (cost=49.23..49.24 rows=1 width=4) -> Seq Scan on transaction_account_postings (cost=0.00..48.12 rows=442 width=4) Filter: ($0 = account_id) (6 rows) -----8<-------------------->8----- -----8<-------------------->8----- EXPLAIN SELECT sum(transaction_account_postings.value) FROM transaction_account_postings INNER JOIN accounts ON transaction_account_postings.account_id = accounts.id WHERE accounts.id = 1; QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=75.40..75.41 rows=1 width=4) -> Nested Loop (cost=0.00..71.59 rows=1520 width=4) -> Index Scan using accounts_pkey on accounts (cost=0.00..8.27 rows=1 width=4) Index Cond: (id = 1) -> Seq Scan on transaction_account_postings (cost=0.00..48.12 rows=1520 width=8) Filter: (transaction_account_postings.account_id = 1) (6 rows) -----8<-------------------->8----- -----8<-------------------->8----- EXPLAIN SELECT sum(transaction_account_postings.value) FROM transaction_account_postings WHERE account_id = 1; QUERY PLAN -------------------------------------------------------------------------------------- Aggregate (cost=51.93..51.94 rows=1 width=4) -> Seq Scan on transaction_account_postings (cost=0.00..48.12 rows=1520 width=4) Filter: (account_id = 1) (3 rows) -----8<-------------------->8----- This absolutely needs to be deferred and only hit the database on request as it is such an expensive operation which is only needed for a handful of front-end logic paths. I will use a property, which is probably what I should have done in the first place rather then try and make column_property do something it isn't really designed to do. Oh well, at least I've improved my understanding of column_property from this experience. Thanks again, 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.
