- changed status to duplicate
relationship options affect an object even when loaded through another relationship
Issue #3681
duplicate
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)
-
repo owner -
repo owner as well as output from master:
#! Item <__main__.Item object at 0x7ff4d57b1550> Item.creator <__main__.User object at 0x7ff4d5cf6790> Item.creator.extra <__main__.Extra object at 0x7ff4d5b36f90> Item.acl_entries <__main__.ACL object at 0x7ff4d5b4b050> <__main__.User object at 0x7ff4d5cf6790> <__main__.Extra object at 0x7ff4d5b36f90>
thanks!
- Log in to comment
Duplicate of
#3431.