CODEWITHSUNDEEP
pythonsqlalchemy
H2O
H2O

Reputation: 662

Backreferencing twice to the same name in sqlalchemy

In my database schema there are matches and teams, a team has a first_opponent and a second_opponent. Now each of these should be available as a backreference from the team. There is no real difference to being first- or second-opponent therefore the backreferences should both have the same name. I can however not simply create two backreferences with the same name.

This are my table definitions (in simplified form):

class Team(Base):
    __tablename__ = "teams"

    id = Column(Integer, primary_key=True)
    name = Column(String)

class Match(Base):
    __tablename__ = "matches"
    id = Column(Integer, primary_key=True)
    first_opponent_id = Column(Integer, ForeignKey("teams.id"))
    second_opponent_id = Column(Integer, ForeignKey("teams.id"))

    first_opponent = relationship("Team", backref=backref('matches'), foreign_keys=[first_opponent_id])
    second_opponent = relationship("Team", backref=backref('matches'), foreign_keys=[second_opponent_id])

This is the error I am getting:

sqlalchemy.exc.ArgumentError: Error creating backref 'matches' on relationship 'Match.second_opponent': property of that name exists on mapper 'Mapper|Team|teams'

Whats the best way to solve this and why does this limitation exist?

Upvotes: 4

Views: 1080

Answers (1)

van
van

Reputation: 77082

There is a limitation because any object can have maximum one property or method with the same name.

You can however do the following:

  1. use two different names
  2. create a third one to use when you need to check all matches. Here you could use simple @property

The code:

class Team(Base):
    __tablename__ = "teams"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    @property
    def matches(self):
        return self.matches_to + self.matches_from


class Match(Base):
    __tablename__ = "matches"
    id = Column(Integer, primary_key=True)
    first_opponent_id = Column(Integer, ForeignKey("teams.id"))
    second_opponent_id = Column(Integer, ForeignKey("teams.id"))

    first_opponent = relationship(
        "Team", backref=backref('matches_to'),
        foreign_keys=[first_opponent_id],
    )
    second_opponent = relationship(
        "Team", backref=backref('matches_from'),
        foreign_keys=[second_opponent_id],
    )

Following test code should work now:

t1, t2, t3 = Team(name='uno'), Team(name='due'), Team(name='tre')
m1 = Match(first_opponent=t1, second_opponent=t2)
m2 = Match(first_opponent=t1, second_opponent=t3)
m3 = Match(first_opponent=t2, second_opponent=t3)
assert 2 == len(t1.matches)
assert 2 == len(t2.matches)
assert 2 == len(t3.matches)
session.add_all([t1, t2, t3])
session.commit()

assert 2 == len(t1.matches)

Upvotes: 6

Related Questions