如何在数据库关系中直接引用表列？

Question

好吧，我在谷歌上找不到答案。我想在一个表里指定两个关系。具体来说，在下面的代码中，我希望User.written字段直接引用Article.author_id字段，因为现在的问题是User.written不知道该引用Article.author_id还是Article.reviewed_by。

错误追踪信息：

>>> User.query.all()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/nick/.virtualenvs/npl/local/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py", line 426, in __get__
    mapper = orm.class_mapper(type)
  File "/home/nick/.virtualenvs/npl/local/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 379, in class_mapper
    mapper = _inspect_mapped_class(class_, configure=configure)
  File "/home/nick/.virtualenvs/npl/local/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 358, in _inspect_mapped_class
    mapper._configure_all()
  File "/home/nick/.virtualenvs/npl/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1145, in _configure_all
    configure_mappers()
  File "/home/nick/.virtualenvs/npl/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2566, in configure_mappers
    raise e
InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers.  Original exception was: Could not determine join condition between parent/child tables on relationship User.written - 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.

我的app/models.py：

class Article(db.Model):
    __tablename__ = 'articles'
    id = db.Column(db.Integer, primary_key=True)
    # Miscellanious details
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))  #<--here
    namespace = db.Column(db.String(6), unique=True)
    date_submitted = db.Column(db.DateTime(), default=datetime.utcnow)
    status = db.Column(db.Enum('pending', 'approved', 'rejected'))
    date_reviewed = db.Column(db.DateTime(), nullable=True)
    reviewed_by = db.Column(db.Integer, db.ForeignKey('users.id'),  #<--here
                            nullable=True)


class User(UserMixin, db.Model):
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))  # Differentiate between users and moderators.
    written = db.relationship('Rental', backref='author', lazy='dynamic')  #<--here

#something like db.relationship(column='articles.author_id') would be great

我在用flask和SQLAlchemy。我希望我提供的信息足够了。提前谢谢你。

Answer 1

使用 foreign_keys 参数来设置 relationship。

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    reviewer_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    author = db.relationship(User, foreign_keys=author_id, backref='written')
    reviewer = db.relationship(User, foreign_keys=reviewer_id, backref='reviewed')

为了简单起见，我把关系放在和外键同一个模型里，这样更容易看出各个部分的位置。 所以现在可以去掉 User.written 这个关系，因为它已经是 Article.author 关系的反向引用了。