“lazy='dynamic'”有什么问题？有哪些替代方案？

Question

这个问题已经在10/17/18完全重写了

为了拥有一个"编辑版本控制系统"(类似于StackOverflow的功能)，我配置了以下类:

tags = db.Table(
    "tags",
    db.Column("tag_id", db.Integer, db.ForeignKey("tag.id")),
    db.Column("post_version_id", db.Integer,
        db.ForeignKey("post_version.id"))
    )

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

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    head_id = db.Column(db.Integer, db.ForeignKey("post_version.id"))

class PostVersion(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    previous_id = db.Column(db.Integer, db.ForeignKey("post_version.id"), default=None)
    pointer_id = db.Column(db.Integer, db.ForeignKey("annotation.id"))
    current = db.Column(db.Boolean, index=True)
    tags = db.relationship("Tag", secondary=tags)

排除无关列，如帖子内容等。实际的数据模型是注释；我已经简化了这些模型以适用于通用性。

实际数据包括136个变量标记和通过编辑版本不同的Post；也就是说：我生成了136个Post。我有15个Tag。最初的136个Post都使用2个Tag进行了一致的标记。然后，我使用我的编辑系统对Post进行了不同的标记（因此对于编辑的Post有多个PostVersion）。

正如你可能注意到的那样，Post和PostVersion之间存在循环引用；我使用这个来配置以下两个关系以进行实验：

关系1 posts

posts = db.relationship("Post",
    secondary="join(tags, PostVersion,"
        "tags.c.post_version_id==PostVersion.id)",
    primaryjoin="Tag.id==tags.c.tag_id",
    secondaryjoin="Post.head_id==PostVersion.id",
    lazy="dynamic")

这基于SQL语句

SELECT
    post.id
FROM
    tag
JOIN
    tags ON tag.id=tags.tag_id
JOIN
    post_version ON tags.post_version_id=post_version.id
JOIN
    post ON post.head_id=post_version.id
WHERE
    tag.id=<tag_id>

和

关系 2 posts2

posts2 = db.relationship("Post",
    secondary="join(tags, PostVersion,"
    "and_(tags.c.post_version_id==PostVersion.id,"
    "AnnotationVersion.current==True))",
    primaryjoin="Tag.id==tags.c.tag_id",
    secondaryjoin="PostVersion.pointer_id==Post.id",
    lazy="dynamic")

基于SQL语句

SELECT
    annotation.id
FROM
    tag
JOIN
    tags ON tag.id=tags.tag_id
JOIN
    annotation_version ON tags.annotation_version_id=annotation_version.id AND 
    annotation_version.current=1
JOIN
    annotation ON annotation_version.pointer_id = annotation.id
WHERE
    tag_id=8;

这会产生以下数据：

Tag         Actual      len(t.posts.all())  len(t.posts.paginate(1,5,False).items)
t1          0           0                   0
t2          1           136                 5
t3          1           136                 5
t8          136         136                 1
t14         136         136                 1
t15         24          136                 1

Tag         Actual      t.posts.count()     t.posts2.count()
t1          0           0                   0
t2          1           136                 163
t3          1           136                 163
t8          136         22168               26569
t14         136         22168               26569
t15         24          3264                3912

我已经排除了冗余标签（即所有其他具有0个帖子的Tag）和相同的数据（即与posts相同的posts2结果）。正如您所看到的，结果存在一些严重的问题！特别是在两个关系中，如果关闭lazy="dynamic"，则始终返回正确的Post。在创建引擎时使用echo=True，@IljaEverilä发现lazy="dynamic"会改变SQL语句。我引用这个问题中的评论：简而言之：lazy="dynamic"将得到FROM post, tags, post_version WHERE ...，但不使用它将得到FROM post, tags JOIN post_version ON tags.post_version_id = post_version.id WHERE ....。正如您所看到的，使用动态设置基本上忽略了您的复合次要条件。现在问题是“为什么？”

---

我的问题：

1. 这是一个错误吗？

2. 我能做些什么来纠正这个困境？

---

更新：

看起来 lazy="dynamic" 在这里明确被反对，但没有提供替代方案。什么是允许分页和计数大集合的替代方案？默认情况下不允许此操作（或者至少在我访问的方式中不允许），文档似乎也没有澄清这个问题！在标题为使用哪种加载方式？的部分中，它似乎推荐对于大集合使用lazy="subquery"加载策略，但这并不允许使用paginate()和count()。

- mas

---

@IljaEverilä 对于返回所有注释来说，这肯定要快得多。但它会消除使用 paginate() 和 count() 的可能性（不过我很好奇，当我仔细阅读你在“生成正确的查询并且计数匹配”时，因为我所知道的在没有 lazy="dynamic" 的情况下计算结果的唯一方法是使用 len()。这是你的意思还是你使用其他方法？现在正在阅读有关 lazy 参数的内容 - mas

简而言之：使用 lazy="dynamic"，您将获得 FROM post, tags, post_version WHERE ...，但是如果不使用，则会获得 FROM post, tags JOIN post_version ON tags.post_version_id = post_version.id WHERE ...。正如您所看到的，使用动态设置时，您的复合次要条件基本上被忽略了。现在问题是“为什么？” - Ilja Everilä

lazy="join" 默认使用 LEFT JOIN，但是您可以通过 innerjoin=True 进行覆盖（默认为 False）。另一方面，这与您当前的困境有点相离远啦 :). - Ilja Everilä

@IljaEverilä：我明白了，而lazy="dynamic"使用了INNER。你是怎么看到lazy="dynamic"查询使用了FROM post, tags JOIN post_version ON tags.post_version_id = post_version.id WHERE ...的呢？有一个函数可以告诉你基本查询是什么吗？你是在查看SQL历史记录吗？ - mas

我在创建引擎时使用了 echo=True，因此发出的 SQL 被记录在日志中。 - Ilja Everilä

Answer 1

这确实是SQLAlchemy在处理动态加载关系的查询时存在的问题。虽然查询应该是

SELECT post.id AS post_id, post.head_id AS post_head_id 
FROM post, tags JOIN post_version ON tags.post_version_id = post_version.id 
WHERE ? = tags.tag_id AND post.head_id = post_version.id

它最终成为了

SELECT post.id AS post_id, post.head_id AS post_head_id 
FROM post, tags, post_version
WHERE ? = tags.tag_id AND post.head_id = post_version.id

因此，虽然在 post 和 post_version 之间存在内部连接（以前的 SQL-92 风格），但 tags 和 post_version 之间的内部连接缺失，因此 tags 和其余部分之间存在CROSS JOIN。结果是查询加载了所有当前的帖子版本，而不考虑标签，因为每个帖子都与 tags 的每一行连接。这也解释了t.posts.count()的乘法。

解决方案是等待修复程序，同时使用其他关系加载策略。

- Ilja Everilä

---

谢谢Ilja提交错误报告。我阅读了相关的要求，但是不想开一个BitBucket账户来进行操作。 - mas