Multiple foreign keys referencing same table column in SQLAlchemy

Question

I am creating a rating system. Rating is a table which contains individual ratings as rows. Each rating has a "rater" and a "ratee". These two columns reference a different table, "User", by means of a foreign key. However, they both reference the same user.id column. Code:

class Rating(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  rater_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
  ratee_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

And here is how they are represented from within the User class (table):

class User(db.Model, UserMixin):
  id = db.Column(db.Integer, primary_key=True)
  username = db.Column(db.String(32), unique=True, nullable=False)
  ratesOfOthers = db.relationship('Rating', backref='rater', lazy=True)
  ratingsByOthers = db.relationship('Rating', backref='ratee', lazy=True)

Now, when I try to use this relationship, I am presented with the following error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.ratesOfOthers - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I have tried using the foreign_keys argument from inside the User class, but that did nothing. Any help would be appreciated.

Answer 1

The assumption is that in your scenario a user can rate other users, and they (themselves) can be rated. Basically, there is one table called User referencing other users within an application.

A relationship in which instances of a class are linked to other instances of the same class is called self-referential relationship, and that is exactly what you have here.

Here is a diagram that represents this self-referential many-to-many relationship that keeps track of ratings:

The Ratings table is the association table of the relationship. The foreign keys in this table are pointing at entries in the User table since it is linking users to users.

To add this table to your database, this is how you can go about it:

ratings = db.Table('ratings'
                   db.Column('my_ratings_id', db.Integer, db.ForeignKey('user.id'))
                   db.Column('other_people_rating_id', db.Integet, db.ForeignKey('user.id'))
                   )

This is an auxiliary table (directly-translated as seen above) that has no data other than foreign keys. It is, therefore, created without an associated model class.

To declare the many-to-many relationship in the User table, add this:

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    
    
    def __repr__(self):
        return f'{self.username}'
        
    my_ratings = db.relationship(
                                 'User',
                                 secondary=ratings,
                                 primaryjoin=(ratings.c.my_ratings_id == id),
                                 secondaryjoin=(ratings.c.other_people_rating_id == id),
                                 backref = db.backref('other_people_rating', lazy='dynamic'), lazy='dynamic'
                                 )

I am defining the relationship as seen from the left side user with the name my_ratings because when you query this relationship from the left side, you will get a list of all those on the right side. Visually, this is what I mean:

Examining all the arguments of the db.relationship() call, you will see that:

• User is the right side entity of the relationship.
• secondary configures the ratings association table
• primaryjoin indicates the condition that links the left side entity with the association table. The user id should match my_ratings_id
• secondaryjoin indicates the condition that links the right side entity with the association table. Again, other_people_rating_id should match the user id
• backref defines how this relationship will be accessed from the right side entity. From the left side, the relationship is named my_ratings, so from the right side, I decided to name it other_people_rating to represent all the left side users that are linked to the target user in the right side.
• The dynamic mode is used to set up the query to not run until specifically requested.
• The second lazy parameter applies to the left side query instead of the right side.