python - SQLAlchemy 中的 contains_eager 和限制

Question

我有两门课:

class A(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship('B')
class B(Base):
    id = Column(Integer, primary_key=True)
    id_a = Column(Integer, ForeignKey('a.id'))
    name = Column(String)

现在我需要包含带有某个名称的 B 的所有对象 A，并且 A 对象将包含过滤后的所有 B 对象。

为了实现它，我构建了查询。

query = db.session.query(A).join(B).options(db.contains_eager(A.children)).filter(B.name=='SOME_TEXT')

现在我只需要 50 个查询项，所以我这样做:

query.limit(50).all()

结果包含少于 50 个，即使没有限制超过 50 个。我读过 The Zen of Eager Loading。但必须有一些技巧才能实现它。我的想法之一是进行 2 个查询。使用innerjoin 来获取ID，然后在第一个查询中使用该ID。

但也许有更好的解决方案。

Answer 1

首先，退后一步看看 SQL。您当前的查询是

SELECT * FROM a JOIN b ON b.id_a = a.id WHERE b.name == '...' LIMIT 50;

请注意，限制是针对 a JOIN b 而不是 a，但如果您将限制放在 a 上，则无法按以下条件进行过滤b 中的字段。这个问题有两种解决方案。第一种是使用标量子查询来过滤 b.name，如下所示:

SELECT * FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.id_a = a.id AND b.name = '...')
LIMIT 50;

这可能效率低下，具体取决于数据库后端。第二种解决方案是在连接后对 a 执行 DISTINCT，如下所示:

SELECT DISTINCT a.* FROM a JOIN b ON b.id_a = a.id
WHERE b.name == '...'
LIMIT 50;

请注意，在这两种情况下，您都没有从 b 获得任何列。我们如何获得它们？再次加入!

SELECT * FROM (
    SELECT DISTINCT a.* FROM a JOIN b ON b.id_a = a.id
    WHERE b.name == '...'
    LIMIT 50;
) a JOIN b ON b.id_a = a.id
WHERE b.name == '...';

现在，在 SQLAlchemy 中编写所有这些:

subquery = (
    session.query(A)
           .join(B)
           .with_entities(A)  # only select A's columns
           .filter(B.name == '...')
           .distinct()
           .limit(50)
           .subquery()  # convert to subquery
)
aliased_A = aliased(A, subquery)
query = (
    session.query(aliased_A)
           .join(B)
           .options(contains_eager(aliased_A.children))
           .filter(B.name == "...")
)