SQLAlchemy multi-table joins

Question

I'm trying to do the following join in SQLAlchemy

select * from splits s
join transactions t on t.guid = s.tx_guid
join accounts a on a.guid = s.account_guid
left join splits s2 on s2.tx_guid = t.guid
join accounts a2 on a2.guid = s2.account_guid
where a.name = 'Checking Account' and t.post_date > '2012-02-29'
order by t.post_date desc

using these models

class Account(db.Model):
 __table__ = db.Table('accounts', db.metadata, autoload=True, autoload_with=db.engine)
 splits = db.relationship('Split', backref='account')
class Transaction(db.Model):
 __table__ = db.Table('transactions', db.metadata, autoload=True, autoload_with=db.engine)
 splits = db.relationship('Split', backref='transaction')
class Split(db.Model):
 __table__ = db.Table('splits', db.metadata,
 db.Column('tx_guid', db.VARCHAR, db.ForeignKey('transactions.guid')),
 db.Column('account_guid', db.VARCHAR, db.ForeignKey('accounts.guid')),
 autoload=True, autoload_with=db.engine)

I got as far as this but now I'm stuck

q = Split.query.join(Transaction). \
 join(Account). \
 options(db.joinedload(Split.transaction)). \
 options(db.joinedload(Split.account)). \
 filter(Account.name == 'Checking Account'). \
 filter(Transaction.post_date > date(2012, 02, 29)). \
 order_by(db.desc(Transaction.post_date))

How do I left join transactions with splits and then those splits with accounts, i.e. last two joins of the query above?

Answer 1

When you want to refer to the same table multiple times in a query, you need to use aliased() construct:

s = db.aliased(Split)
a = db.aliased(Account)
q = db.session.query(Split).\
 options(joinedload(Split.transaction)).\
 options(joinedload(Split.account)).\
 join(Transaction).\
 join(Account).\
 outerjoin(s, Transaction.splits).\
 join(a, a.guid == s.account_guid).\
 filter(Account.name == 'Checking Account').\
 filter(Transaction.post_date > date(2012, 2, 29)).\
 order_by(Transaction.post_date.desc())

And be careful when mixing INNER and OUTER joins.