Mixin relationship defaulting to uselist=False unexpectedly
David Bowser
class UserMixin(object):
@_declared_attr
def user_id(cls):
return sa.Column('user_id',typ.Integer,sa.ForeignKey("tg_user.user_id", ondelete="CASCADE"),nullable=False)
@_declared_attr
def user(cls):
return orm.relationship("User",primaryjoin='%s.user_id == User.user_id'%cls.__name__)
@_declared_attr
def user_profile(cls):
return orm.relationship('UserProfile',primaryjoin="%s.user_id == UserProfile.id"%cls.__name__,
foreign_keys=lambda:[metadata.tables['user_profiles'].c.id],
backref=cls.__tablename__ if cls.__tablename__ else tablename(cls.__name__))
when I use my mixin in say a class like this:
class JournalEntry(UserMixin,DeclarativeBase):
__tablename__ = 'journal_entries'
#{ Columns
id = Column(Integer,primary_key=True)
is_private = Column(Boolean,default=False)
modified = Column(DateTime,onupdate=datetime.datetime.now)
text = Column(UnicodeText)
#}
And then I:
>>> from myProject.model import *
>>> profile = DBSession.query(UserProfile).get(1)
>>> profile.journal_entries
I keep getting the error:
/Users/Me/Documents/Work/Projects/myProject/myProject/lib/python2.7/site-packages/SQLAlchemy-0.7.6-py2.7-macosx-10.7-intel.egg/sqlalchemy/orm/strategies.py:508: SAWarning: Multiple rows returned with uselist=False for lazily-loaded attribute 'UserProfile.journal_entries'
< myProject.model.user_data.JournalEntry object at 0x10d8b26d0>
I can't figure out why it's using uselist=False, and a joined lazy load. Any help pointing me in the right direction would be great.
~Dave
Michael Bayer
it's probably that foreign_keys in your relationship there, which appears to refer to user_profiles.c.id. that's the column which is referred to here, and that parameter would instead be referring to the "user_id" column, i.e. the one that has the foreign key back to the parent row. It's equivalent to the column that would have ForeignKey set up on it here; I guess you have some multiple foreign keys happening here? tg_user.user_id is not the same as UserProfile.id ? This suggests some potential design problems, what is the value of JournalEntries.user_id if it points to row "3" in tg_user.user_id and row "5" in user_profile.id ?
David Bowser
The table tg_user only contains the authentication/access data for the user, and is queried extremely frequently. The table user_profile contains, well everything else in the profile that isn't needed very often. For any user with user_id n, that user's user_profile id is also n.
Easy Fix though once you pointed out that was the problem.
Dropped the second relationship, and used joined table inheritance without a discriminator, and the orm is now happy.
Still confused about how it ended getting a uselist=False on the backref for that tho.
Michael Bayer
On Apr 24, 2012, at 4:42 PM, David Bowser wrote:
>
> The table tg_user only contains the authentication/access data for the user, and is queried extremely frequently. The table user_profile contains, well everything else in the profile that isn't needed very often. For any user with user_id n, that user's user_profile id is also n.
>
> Easy Fix though once you pointed out that was the problem.
>
> Dropped the second relationship, and used joined table inheritance without a discriminator, and the orm is now happy.
>
> Still confused about how it ended getting a uselist=False on the backref for that tho.
one-to-many is defined as parent->child where child has a foreign key column referring to parent; many-to-one is the reverse, where parent has a foreign key that refers to child. When you tell the ORM "foreign_keys = [some_col_on_parent]", that tells SQLA that the parent refers to the child, hence many-to-one, hence uselist is set to False.
David Bowser
one-to-many is defined as parent->child where child has a foreign key column referring to parent; many-to-one is the reverse, where parent has a foreign key that refers to child. When you tell the ORM "foreign_keys = [some_col_on_parent]", that tells SQLA that the parent refers to the child, hence many-to-one, hence uselist is set to False.
Michael Bayer
David Bowser
def user_profile(cls):
return orm.relationship('UserProfile',primaryjoin="%s.user_id == UserProfile.id"%cls.__name__,
foreign_keys=lambda:[metadata.tables['user_profiles'].c.id],
backref=cls.__tablename__ if cls.__tablename__ else tablename(cls.__name__))