lazy loading should figure out simple correlations, not trip up

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy import exceptions
import datetime

meta = MetaData(create_engine('sqlite://', echo=True))


users = Table('users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(50))
    )

stuff = Table('stuff', meta,
    Column('id', Integer, primary_key=True),
    Column('date', Date),
    Column('user_id', Integer, ForeignKey('users.id')))


meta.create_all()

users.insert().execute(
    {'id':1, 'name':'user1'},
    {'id':2, 'name':'user2'},
    {'id':3, 'name':'user3'},
)

stuff.insert().execute(
    {'id':1, 'user_id':1, 'date':datetime.date(2007, 10, 15)},
    {'id':2, 'user_id':1, 'date':datetime.date(2007, 12, 15)},
    {'id':3, 'user_id':1, 'date':datetime.date(2007, 11, 15)},
    {'id':4, 'user_id':2, 'date':datetime.date(2008, 1, 15)},
    {'id':5, 'user_id':3, 'date':datetime.date(2007, 6, 15)},
)

class User(object):
    def __repr__(self):
        return "User %r, stuff: %r" % (self.id, self.stuff)

class Stuff(object):
    def __repr__(self):
        return "Stuff %r" % self.id

mapper(Stuff, stuff)

stuff_view = select([stuff.c.id](stuff.c.id)).where(stuff.c.user_id==users.c.id).correlate(users).order_by(desc(stuff.c.date)).limit(1)

mapper(User, users, properties={
    'stuff':relation(Stuff, primaryjoin=and_(users.c.id==stuff.c.user_id, stuff.c.id==(stuff_view.as_scalar())))
})

sess = create_session()

# bug in SA with lazy load
#print sess.query(User).all()
#sess.clear()

print sess.query(User).options(eagerload('stuff')).all()
Comments (2)
