sqlalchemy foreign key relationship attributes

Question

I have a User table and a Friend table. The Friend table holds two foreign keys both to my User table as well as a status field. I am trying to be able to call attributes from my User table on a Friend object. For example, I would love to be able to do something like, friend.name, or friend.email.

class User(Base):     """ Holds user info """      __tablename__ = 'user'     id = Column(Integer, primary_key=True)     name = Column(String(25), unique=True)     email = Column(String(50), unique=True)     password = Column(String(25))     admin = Column(Boolean)      # relationships     friends = relationship('Friend', backref='Friend.friend_id',primaryjoin='User.id==Friend.user_id', lazy='dynamic')  class Friend(Base):     __tablename__ = 'friend'      user_id = Column(Integer, ForeignKey(User.id), primary_key=True)     friend_id = Column(Integer, ForeignKey(User.id), primary_key=True)     request_status = Column(Boolean) 

When I get friend objects all I have is the 2 user_ids and i want to display all properties of each user so I can use that information in forms, etc. I am new to sqlalchemy - still trying to learn more advanced features. This is just a snippet from a larger Flask project and this feature is going to be for friend requests, etc. I've tried to look up association objects, etc, but I am having a hard with it.

Any help would be greatly appreciated.

Answer 1

First, if you're using flask-sqlalchemy, why are you using directly sqlalchemy instead of the Flask's db.Model?

I strongly reccomend to use flask-sqlalchemy extension since it leverages the sessions and some other neat things.

Creating a proxy convenience object is straightforward. Just add the relationship with it in the Friend class.

class Friend(Base):     __tablename__ = 'friend'      user_id = Column(Integer, ForeignKey(User.id), primary_key=True)     friend_id = Column(Integer, ForeignKey(User.id), primary_key=True)     request_status = Column(Boolean)      user = relationship('User', foreign_keys='Friend.user_id')     friend = relationship('User', foreign_keys='Friend.friend_id') 

SQLAlchemy will take care of the rest and you can access the user object simply by:

name = friend.user.name 

If you plan to use the user object every time you use the friend object specify lazy='joined' in the relationship. This way it loads both object in a single query.