Filed in SQLAlchemy of user's list

Question

I have one model that is Users in which there is a field in this model that I would like to store a list of Users. The idea is that you can add frieds and store them somewhere.

class User (db.Model):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique = True)
    email = Column(String(120), unique = True)
    password = Column(String(50))
    date = Column(DateTime(), default=datetime.now())
    friends = "Should be a list of users"

I have thought to have a string with the id of each user but, is there any posibility to do it with a relationship to the same model? like this:

friends = relationship("User")

Thanks a lot!

score 2 · Accepted Answer · answered Jan 26 '14 at 17:46

Proposed solutions based on Adjacency List Relationships would only work in case when someone can be a friend of maximum one person, which I do not believe to be the case in the real world.

A pattern you need to apply in this case is called Self-Referential Many-to-Many Relationship. Please read the sample linked to above. In order to make it work for your model, you would need to create additional table to keep the pairs of friends, and configure the relationship as below:

# object model
t_userfriend = Table("user_friend", Base.metadata,
    Column("user_id", Integer, ForeignKey("users.id"), primary_key = True),
    Column("friend_id", Integer, ForeignKey("users.id"), primary_key = True),
)

class User (Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique = True)
    # ...
    friends = relationship("User",
                          secondary = t_userfriend,
                          primaryjoin = (id == t_userfriend.c.user_id),
                          secondaryjoin = (id == t_userfriend.c.friend_id),
                          backref = "friend_of",
                          )

I guess that the other question you need to ask yourself is whether in your model if A is a friend of B, does this mean that B is a friend of A? In case this is true, you might want/need to:

either store just one side of the relationship, and calculate the other
make sure you always store both sides to the relationship

Thanks!! that is what I need! A can add B as a friend, but it does not mean that A is added to the B's friends! Thanks again ;) — Alvaro Garcia de la Villa, Jan 30 '14 at 10:20

score 1 · Answer 2 · edited May 23 '17 at 12:21

1

You can use Adjacency List Relationship and this link have the same issue so you can learn from it. How to create relationship many to many in SQLAlchemy (python, flask) for model User to itself

edited May 23 '17 at 12:21

Community

1
1

answered Jan 25 '14 at 15:05

Mohamed Abd El Raouf

918
1
9
21

score 0 · Answer 3 · answered Jan 25 '14 at 14:57

Yes you can do it with Adjacency List Relationships.

class User (db.Model):

    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique = True)
    email = Column(String(120), unique = True)
    password = Column(String(50))
    date = Column(DateTime(), default=datetime.now())
    friends = relationship("User",
            backref=backref('parent', remote_side=[id])
        )

Filed in SQLAlchemy of user's list

3 Answers3