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

Question

好吧，我的 google-fu 失败了。我想在一个表中指定 2 个关系。具体来说，在下面的代码中，我希望 User.writing 字段直接引用 Article.author_id 字段，因为问题似乎是 User.writing 无法决定是引用 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.

我的应用程序/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 argument to 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.writing 关系现在它是 Article.author 关系的 backref