python - AmbiguousForeignKeysError : Couldn't determine join condition between parent/child tables on relationship User. posts - 多个外键路径

Question

如何实现用户对帖子的点赞功能？我关注了其他在线用户发布的代码；但是，我收到以下错误:

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

所有案例都在文档中进行了描述:https://docs.sqlalchemy.org/en/13/orm/join_conditions.html

在您的情况下，问题出在 Post.likes:sqlalchemy 无法确定如何加入，因为 Post 中有 2 个字段是 FK-s 到 user.id。所以你必须为它指定 primaryjoin 或者你应该只创建“用户上的字段 with backref="posts":

• 使用 primaryjoin 参数:

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")

• 使用 User.author 字段:

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')
    ...