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.
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.
