AmbiguousForeignKeysError: Couldn't determine join condition between parent/child tables on relationship User.posts - multiple foreign key paths

Question

How to implement a like function for a User to a Post? I have followed code posted by other online users; however, I am getting the following error:

AmbiguousForeignKeysError: 
Could not determine join condition between parent/child tables on relationship 
User.posts - 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

class User(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   username = db.Column(db.String(20), unique=True, nullable=False)
   email = db.Column(db.String(120), unique=True, nullable=False)
   image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
   password = db.Column(db.String(60), nullable=False) 
   posts = db.relationship('Post', backref='author', lazy=True)

   def __repr__(self):
       return "{}, {}, {}".format(self.username, self.email, self.image_file)

   liked = db.relationship(
       'PostLike',
       foreign_keys='PostLike.user_id',
       backref='user', lazy='dynamic')


   def like_post(self, post):
       if not self.has_liked_post(post):
           like = PostLike(user_id=self.id, post_id=post.id)
           db.session.add(like)

   def unlike_post(self, post):
       if self.has_liked_post(post):
           PostLike.query.filter_by(
               user_id=self.id,
               post_id=post.id).delete()

   def has_liked_post(self, post):
       return PostLike.query.filter(
           PostLike.user_id == self.id,
           PostLike.post_id == post.id).count() > 0

class PostLike(db.Model):
   __tablename__ = 'post_like'
   id = db.Column(db.Integer, primary_key=True)
   user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
   post_id = db.Column(db.Integer, db.ForeignKey('post.id'))


class Post(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   title = db.Column(db.String(100), default=' ')
   date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
   content = db.Column(db.Text, nullable=False)
   author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
   recipient_id = db.Column(db.Integer, db.ForeignKey('user.id'))
   likes = db.relationship('PostLike', backref='post', lazy='dynamic')

   def __repr__(self):
       return "Post(Title:'{}', Content:'{}')".format(self.title, self.content)

Answer 1

All the cases are described in docs: https://docs.sqlalchemy.org/en/13/orm/join_conditions.html

In your case the problem is with Post.likes: sqlalchemy can't determine how to join because there are 2 fields in Post that are FK-s to user.id. So you must specify primaryjoin for it OR you should only create "field on User with backref="posts":

• using primaryjoin argument:

class User(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   username = db.Column(db.String(20), unique=True, nullable=False)
   email = db.Column(db.String(120), unique=True, nullable=False)
   image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
   password = db.Column(db.String(60), nullable=False) 
   posts = db.relationship('Post', primaryjoin="User.id==Post.author_id")

• using User.author field:

class User(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   username = db.Column(db.String(20), unique=True, nullable=False)
   email = db.Column(db.String(120), unique=True, nullable=False)
   image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
   password = db.Column(db.String(60), nullable=False) 
   # NO posts field explicitly defined!

class Post(db.Model):
    ...
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    recipient_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    author = db.relationship('User', backref='posts', foreign_keys=[author_id])
    recipient = db.relationship('User', backref='incoming_posts', foreign_keys=[recipient_id])

    # User will have 2 backref relationships: 
    # * `posts` -- authored posts,
    # * `incoming_posts` -- posts where user is recipient

    likes = db.relationship('PostLike', backref='post', lazy='dynamic')
    ...