Lazy clause not optimized when foreign key points to mapped property

Full description of problem and workaround at http://groups.google.com/group/sqlalchemy/browse_thread/thread/5f88aa6baec0583a

If a lazy loader is based on a foreign key that is pointing at a mapped property rather than an actual Column object, it doesn't detect that this is a simple 'get'-style query that can be optimised by converting to query.get().

Example showing the problem:

import sqlalchemy as sa 
import sqlalchemy.orm as saorm 
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base() 
Base.metadata.bind = sa.create_engine('sqlite:///:memory:')

class Master(Base): 
    __tablename__ = 'master' 
    id = sa.Column(sa.Integer, primary_key=True)

class Detail(Base): 
    __tablename__ = 'detail' 
    id = sa.Column(sa.Integer, primary_key=True) 
    master_id = sa.Column(None, sa.ForeignKey(Master.id)) 
    master = saorm.relation(Master, backref='details')

def dbg(msg): 
    print "\n######## %s ########" % msg

def test(): 
    dbg('SQLAlchemy version: %s' % sa.__version__) 
    Base.metadata.create_all() 
    Session = saorm.sessionmaker() 
    sess = Session()

    m = Master() 
    for i in range(10): 
        m.details.append(Detail()) 
    sess.add(m) 
    sess.commit() 
    sess.close()

    Base.metadata.bind.echo = True 
    sess = Session() 
    dbg("Getting Master") 
    m = sess.query(Master).first() 
    dbg("Getting details") 
    details = m.details 
    dbg("Getting master of first detail") 
    m2 = details[0](0).master 
    assert m2 is m 
    dbg("Getting master again via query.get") 
    m3 = sess.query(Master).get(m.id) 
    assert m3 is m2

if __name__ == '__main__': 
    test()

and output:

######## SQLAlchemy version: 0.5.5 ########

######## Getting Master ######## 
2009-08-03 13:17:12,445 INFO sqlalchemy.engine.base.Engine.0x...7ecL 
BEGIN 
2009-08-03 13:17:12,447 INFO sqlalchemy.engine.base.Engine.0x...7ecL 
SELECT master.id AS master_id 
FROM master 
 LIMIT 1 OFFSET 0 
2009-08-03 13:17:12,447 INFO sqlalchemy.engine.base.Engine.0x...7ecL [

######## Getting details ######## 
2009-08-03 13:17:12,451 INFO sqlalchemy.engine.base.Engine.0x...7ecL 
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id 
FROM detail 
WHERE ? = detail.master_id 
2009-08-03 13:17:12,451 INFO sqlalchemy.engine.base.Engine.0x...7ecL [1](])

######## Getting master of first detail ######## 
2009-08-03 13:17:12,456 INFO sqlalchemy.engine.base.Engine.0x...7ecL 
SELECT master.id AS master_id 
FROM master 
WHERE master.id = ? 
2009-08-03 13:17:12,457 INFO sqlalchemy.engine.base.Engine.0x...7ecL [1](1)

######## Getting master again via query.get ########

######## Finished ########

If you change the master_id column definition like this:

    master_id = sa.Column(None, sa.ForeignKey(Master.__table__.c.id))

...it works as expected.

Comments (3)