多个外键上的SQLAlchemy双内部联接

Question

请查看底部的更新

我有三节课。我们把它们称为Post，PostVersion，和{}。（这是针对web应用中的内部版本控制系统，可能类似于StackOverflow，不过我不确定它们的实现策略）。我用git的术语来理解它。为了这个问题的目的，这些是类的高度简化版本：

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    author = db.relationship("User", backref="posts")
    head_id = db.Column(db.Integer, db.ForeignKey("post_version.id"))
    HEAD = db.relationship("PostVersion", foreign_keys=[head_id])
    added = db.Column(db.DateTime, default=datetime.utcnow)

class PostVersion(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    editor_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    editor = db.relationship("User")
    previous_id = db.Column(db.Integer, db.ForeignKey("post_version.id"), default=None)
    previous = db.relationship("PostVersion")
    pointer_id = db.Column(db.Integer, db.ForeignKey("post.id"))
    pointer = db.relationship("Post", foreign_keys=[pointer_id])
    post = db.Column(db.Text)
    modified = db.Column(db.DateTime, default=datetime.utcnow)
    tag_1_id = db.Column(db.Integer, db.ForeignKey("tag.id"), default=None)
    tag_2_id = db.Column(db.Integer, db.ForeignKey("tag.id"), default=None)
    tag_3_id = db.Column(db.Integer, db.ForeignKey("tag.id"), default=None)
    tag_4_id = db.Column(db.Integer, db.ForeignKey("tag.id"), default=None)
    tag_5_id = db.Column(db.Integer, db.ForeignKey("tag.id"), default=None)
    tag_1 = db.relationship("Tag", foreign_keys=[tag_1_id])
    tag_2 = db.relationship("Tag", foreign_keys=[tag_2_id])
    tag_3 = db.relationship("Tag", foreign_keys=[tag_3_id])
    tag_4 = db.relationship("Tag", foreign_keys=[tag_4_id])
    tag_5 = db.relationship("Tag", foreign_keys=[tag_5_id])

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tag = db.Column(db.String(128))

为了创建一个新的post，我创建了一个post和一个PostVersion指向的初始Post.head_id。每次进行编辑时，都会创建一个指向上一个PostVersion的新的PostVersion，而{}则重置为指向新的PostVersion。要将post版本重置为早期版本——嗯，我还没有走到那么远，但是复制以前的版本或只是将指针重置到以前的版本似乎很简单。在

不过，我的问题是：如何编写Post和{}之间的关系，使之

1. Post.tags将是当前{}包含的所有标记的列表，并且
2. Tag.posts将是当前具有该特定标记的所有{}的列表？在

第一个条件似乎很简单，一个简单的方法

^{pr2}$

这个技巧暂时还可以，但第二个条件现在对我来说几乎是棘手的。我目前在Tag中使用了一种令人讨厌的方法，我使用一个or_过滤器查询带有该标记的所有PostVersion：

^{3}$

这是非常低效的，我不能分页的结果。在

我知道这将是从Post到Tag或{}到{}到{}的二次连接，但它必须是or上的二次连接，我甚至不知道如何开始编写它。在

回顾我的代码，我开始想知道为什么有些关系需要定义foreign_keys参数，而另一些则不需要。我认为这与它们的定义位置有关（是否紧跟在FK id列之后），并注意到有一个foreign_keys的列表，我在想这就是我应该如何定义的它。但我不知道该怎么做。在

我现在还想知道是否可以用一个配置良好的关系来免除pointer_id上的PostVersion。然而，这与问题无关（尽管循环引用确实会引起头痛）。在

作为参考，我使用了Flask SQLAlchemy、Flask migrate和MariaDB。我非常关注Miguel Grinberg's Flask Megatutorial。在

任何帮助或建议都是天赐之物。在

更新

我设计了以下mysql查询，它的工作原理是，现在我需要将其转换为sqlalchemy：

SELECT
    post.id, tag.tag 
FROM
    post
INNER JOIN
    post_version
ON
    post.head_id=post_version.id
INNER JOIN 
    tag
ON 
    post_version.tag_1_id=tag.id OR
    post_version.tag_2_id=tag.id OR
    post_version.tag_3_id=tag.id OR
    post_version.tag_4_id=tag.id OR
    post_version.tag_5_id=tag.id OR
WHERE
    tag.tag="<tag name>";

Answer 1

我自己解决了这个问题，它实际上只包括在主连接中定义一个主连接和一个辅助连接：

posts = db.relationship("Post", secondary="post_version",
    primaryjoin="or_(Tag.id==post_version.c.tag_1_id,"
    "Tag.id==post_version.c.tag_2_id,"
    "Tag.id==post_version.c.tag_3_id,"
    "Tag.id==post_version.c.tag_4_id,"
    "Tag.id==post_version.c.tag_5_id)",
    secondaryjoin="Annotation.head_id==post_version.c.id",
    lazy="dynamic")

我可以把名字和表格混在一起。我会在实验时更新答案，使之更具规律性。在

Answer 2

你能改变数据库的设计吗，还是必须让你的应用程序在你不能改变的数据库上工作？如果是后者，我帮不了你。如果你能改变设计，你应该这样做：

1. 用Post-to-PostVersions之间的一对多关系替换PostVersions的链接链。您的“Post”类最终将与与该Post相关的PostVersion的所有实例具有关系“versions”。

2. 使用附加的关联表将tag_id成员替换为多对多关系。

这两种方法在SQLAlchemy文档中都有很好的解释。一定要从最少的代码开始，在小型的非烧瓶命令行程序中进行测试。一旦你完成了基本功能，就把这个概念转移到更复杂的类中。之后，再问自己最初的问题。答案会来得容易得多。在