One object two foreign keys to the same table

Question

I need to have a post associated to two users. The author and the moderator. I am trying without success this code

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    ...
    post = db.relationship('Post', foreign_keys=['posts.id'], backref='post_user', lazy='dynamic')
    post_blame = db.relationship('Post', foreign_keys=['posts.moderated_by'], backref='post_blame', lazy='dynamic')    

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    ...
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    moderated_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

Error:

ArgumentError: Column-based expression object expected for argument 'foreign_keys'; got: 'posts.id', type <class 'str'>

Answer 1

One of the issues here is that you are currently trying to use table columns in the relationship foreign_keys, rather than class attributes.

That is, instead of using posts.id, you should be using Post.id. (In fact, to refer to a table column, you would need to use posts.c.id).

So, it is possible that your original code will work if you correct it to:

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    ...
    post = db.relationship('Post', foreign_keys='Post.id', backref='post_user', lazy='dynamic')
    post_blame = db.relationship('Post', foreign_keys='Post.moderated_by', backref='post_blame', lazy='dynamic')    

If it does not, then there several other options. First, you could establish these relationships in the Post class, where it is less ambiguous for sqlalchemy to find the foreign key relationship. Something like

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    ...
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    moderated_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    post_user = db.relationship(User, foreign_keys=author_id, backref='post', lazy='dynamic')
    post_blame = db.relationship(User, foreign_keys=moderated_by, backref='post_blame', lazy='dynamic')    

Note in this version, we don't need to pass the foreign_keys value as a string, we can just refer directly to the column in scope.

Alternatively, if you wish to establish these relationships within User, you may you need to give sqlalchemy more information, by using primaryjoin ... perhaps something like:

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    ...
    post = db.relationship('Post', primaryjoin='User.id == Post.id', backref='post_user', lazy='dynamic')
    post_blame = db.relationship('Post', foreign_keys='User.id == Post.moderated_by', backref='post_blame', lazy='dynamic')