fine grained eager load support
Issue #777
resolved
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()
accounts_table = Table('accounts', metadata,
Column('account_id', Integer, primary_key=True),
Column('name', String(40)),
)
transactions_table = Table('transactions', metadata,
Column('transaction_id', Integer, primary_key=True),
Column('name', String(40)),
)
entries_table = Table('entries', metadata,
Column('entry_id', Integer, primary_key=True),
Column('name', String(40)),
Column('account_id', Integer, ForeignKey(accounts_table.c.account_id)),
Column('transaction_id', Integer, ForeignKey(transactions_table.c.transaction_id)),
)
metadata.create_all(engine)
class Account(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "<Account(%r)>" % self.name
class Transaction(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "<Transaction(%r)>" % self.name
class Entry(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "<Entry(%r)>" % self.name
mapper(Account, accounts_table)
mapper(Transaction, transactions_table)
mapper(Entry, entries_table, properties = dict(
account = relation(Account, uselist=False, backref=backref('entries', lazy=True)),
transaction = relation(Transaction, uselist=False, backref=backref('entries', lazy=False, join_depth=3)),
))
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
session = Session()
# Account 1 with its entries
acc1 = Account('acc1')
ent11 = Entry('ent11')
ent12 = Entry('ent12')
acc1.entries.append(ent11)
acc1.entries.append(ent12)
# Account 2 with its entries
acc2 = Account('acc2')
ent21 = Entry('ent21')
ent22 = Entry('ent22')
acc2.entries.append(ent21)
acc2.entries.append(ent22)
# Make connection between both accounts with transactions
tx1 = Transaction('tx1')
tx1.entries.append(ent11)
tx1.entries.append(ent21)
tx2 = Transaction('tx2')
tx2.entries.append(ent12)
tx2.entries.append(ent22)
session.save(acc1)
session.flush()
session.clear()
# I want to retrieve in ONE select all transactions engaged with acc1 and the account.name of each entry:
acc = session.query(Account).options(eagerload_all('entries.transaction.entries.account')).first()
assert acc.name == 'acc1'
assert acc.entries[0](0).transaction.entries[0](0).account.name == 'acc1' # no new SELECT, that's fine.
assert acc.entries[0](0).transaction.entries[1](1).account.name == 'acc2' # <== execute a new SELECT to retrieve all entries of acc2 (I just want 'acc2')
Comments (3)
-
reporter -
reporter - changed status to resolved
all done....0282dc2738bee95901ade0374679e10f9b494898.
-
reporter - removed milestone
Removing milestone: 0.4.xx (automated comment)
- Log in to comment
to do this requires another major refactoring of the mechanics of query/mapper/interfaces/strategies, which builds upon the major refactorings we've done so far in 0.4. We've gotten the idea of a "path" introduced using the LoaderStack, where EagerLoader can track its current position against an AliasedClauses object and the correct row decorator to use. this concept has to be expanded into the StrategizedProperty and PropertyOption classes so that mapper options are keyed by path and not the actual property instance. Also, the "path" tracked by AliasedClauses should be replaced by one consistent "path building" concept, which occurs both within setup()/setup_query() as well as _instance()/populate_instance(). The idea of hanging attributes and options off a particular path should be strongly implemented.
this fragment of a patch, which changes just a small number of the actual amount of code that would have to change, suggests the direction to go: