CODEWITHSUNDEEP
pythonsqlalchemy
Artem Zaytsev
Artem Zaytsev

Reputation: 1695

SQLAlchemy. 2 different relationships for 1 column

I have a simple many-to-many relationship with associated table: enter image description here

with following data:

matches:enter image description here users:enter image description here

users_mathces:enter image description here

ONE user can play MANY matches and
ONE match can involve up to TWO users
I want to realize proper relationships in both "Match" and "User" classes

users_matches_table = Table('users_matches', Base.metadata,
    Column('match_id', Integer, ForeignKey('matches.id', onupdate="CASCADE", ondelete="CASCADE")),
    Column('user_id', Integer, ForeignKey('users.id', onupdate="CASCADE", ondelete="CASCADE"))
    )

class Match(Base):
    __tablename__ = 'matches'
    id = Column(Integer, primary_key=True)

    #relations
    user1 = relationship('User', secondary = users_matches_table)
    user2 = relationship('User', secondary = users_matches_table)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)

    #relations
    matches = relationship('Match', secondary=users_matches_table)

But obviously, there should be some rules for Match.user1 and Match.user2 relationships, that will differ one User from another, so i could get match1.user1 and match1.user2 and they won't reference to the same User. Any ideas how to do that?

Upvotes: 2

Views: 45

Answers (1)

matino
matino

Reputation: 17715

I'm afraid you can't do it like this. I suggest you have just one relationship users and validate the insert queries.

Upvotes: 1

Related Questions