relationship options affect an object even when loaded through another relationship

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *


Base = declarative_base()


class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    creator_id = Column(
        Integer,
        ForeignKey('users.id'),
        index=True,
        nullable=True
    )
    creator = relationship(
        'User',
        lazy=True,
        backref=backref(
            'created_items',
            lazy=True
        )
    )
    acl_entries = relationship(
        'ACL',
        lazy=True,
        backref=backref(
            'item',
            lazy=True
        )
    )


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    extra_id = Column(
        Integer,
        ForeignKey('extra.id'),
        index=True,
        nullable=False
    )
    extra = relationship(
        'Extra',
        lazy=True,
        backref=backref(
            'user',
            lazy=True,
            uselist=False
        )
    )


class Extra(Base):
    __tablename__ = 'extra'
    id = Column(Integer, primary_key=True)


class ACL(Base):
    __tablename__ = 'acl_entries'
    id = Column(Integer, primary_key=True)
    item_id = Column(
        Integer,
        ForeignKey('items.id'),
        index=True,
        nullable=True
    )
    user_id = Column(
        Integer,
        ForeignKey('users.id'),
        index=True,
        nullable=True
    )
    user = relationship(
        'User',
        lazy=True,
        backref=backref(
            'acl_entries',
            lazy=True
        )
    )


e = create_engine('sqlite:///:memory:', echo=True)
# e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)


item = Item()
user = User(extra=Extra())
item.creator = user
item.acl_entries = [ACL(user=user)]
s.add(item)
s.flush()
s.expire_all()

print


query = (s.query(Item)
         .options(joinedload('acl_entries').joinedload('user').noload('extra'),
                  joinedload('creator').joinedload('extra')))
for item in query:
    print 'Item', item
    print 'Item.creator', item.creator
    print 'Item.creator.extra', item.creator.extra
    print 'Item.acl_entries'
    for ae in item.acl_entries:
        print ae, ae.user, ae.user.extra

Output:

Item <__main__.Item object at 0x7f2df31d5450>
Item.creator <__main__.User object at 0x7f2df31f9050>
Item.creator.extra None
Item.acl_entries
<__main__.ACL object at 0x7f2df316ecd0> <__main__.User object at 0x7f2df31f9050> None

I would expect Item.creator.extra to be available. This example is based on a real application where I don't care about certain User relationships when loading the ACL for an object (so i noload them), but at the same time I do need the data from the relationship for the "creator" of the object (which is a relationship between Item and User).

The data is actually queried, but apparently discarded somewhere within SQLAlchemy:

SELECT items.id AS items_id,
       items.creator_id AS items_creator_id,
       extra_1.id AS extra_1_id,
       users_1.id AS users_1_id,
       users_1.extra_id AS users_1_extra_id,
       users_2.id AS users_2_id,
       users_2.extra_id AS users_2_extra_id,
       acl_entries_1.id AS acl_entries_1_id,
       acl_entries_1.item_id AS acl_entries_1_item_id,
       acl_entries_1.user_id AS acl_entries_1_user_id
FROM items
LEFT OUTER JOIN users AS users_1 ON users_1.id = items.creator_id
LEFT OUTER JOIN extra AS extra_1 ON extra_1.id = users_1.extra_id
LEFT OUTER JOIN acl_entries AS acl_entries_1 ON items.id = acl_entries_1.item_id
LEFT OUTER JOIN users AS users_2 ON users_2.id = acl_entries_1.user_id

Comments (2)