SQL证书化学ORM不存在：表别名范围

Question

我有三个实体， ， ， ， 其中链接到 s （）。我想找到那些没有实例的实例，该实例未连接到.ABCCABA-*C-BACB

我无法提出一个SQLAlchemy查询来为我做到这一点，我开始认为编译器有问题。

以下单元测试对此进行了说明：

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

from sqlalchemy import Column, Integer, create_engine, literal

_sql_engine = create_engine('sqlite:///:memory:')

session = sessionmaker(bind=_sql_engine)()

def test_model():

    Base = declarative_base()

    class A(Base):

        __tablename__ = 'a'

        id = Column(Integer, primary_key=True)

    class B(Base):

        __tablename__ = 'b'

        id = Column(Integer, primary_key=True)

    class C(Base):

        __tablename__ = 'c'

        a = Column(Integer, primary_key=True)

        b = Column(Integer, primary_key=True)

    Base.metadata.create_all(_sql_engine)

    a = [A(id=10),      A(id=20)]

    c = [C(a=10, b=11), C(a=20, b=21)]

    b = [B(id=11)]

    session.add_all(a + c + b)

    session.commit()

    q = session.query(A).filter(

        A.id < literal(100),

        ~(

            session.query(C)

                .filter(

                A.id == C.a,

                ~(

                    session.query(B)

                    .filter(

                        B.id == C.b,

                    ).exists()

                )

            ).exists()

        )

    )

    print(q.statement)

    print(len(q.all()))

    assert len(q.all()) == 1

测试期望一个结果，但它得到零。打印的 SQL 语句是

SELECT a.id 

FROM a 

WHERE a.id < :param_1 AND NOT (EXISTS (SELECT 1 

FROM c 

WHERE NOT (EXISTS (SELECT 1 

FROM b, a 

WHERE b.id = c.b AND a.id = c.a))))

现在，在我看来，问题出在第三个语句上。 并覆盖上面的别名，并与前面的约束断开连接。FROMba

这是正确的吗？这就是 SQL 中的作用域的工作方式吗？如果是这样，我是在使用SQL算法时犯了错误，还是这是一个错误？

（单元测试使用 SQLite，但最终结果应在后灰色SQL 中运行。

Answer 1

多亏了 github 上的 zzzeek，我找到了答案：别名在第二次嵌套后在子查询中隐藏。如果我们不想要这个，我们使用相关的子查询。固定单元测试：from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker, aliasedfrom sqlalchemy import Column, Integer, create_engine, literal_sql_engine = create_engine('sqlite:///:memory:')session = sessionmaker(bind=_sql_engine)()def test_model():&nbsp; &nbsp; Base = declarative_base()&nbsp; &nbsp; class A(Base):&nbsp; &nbsp; &nbsp; &nbsp; __tablename__ = 'a'&nbsp; &nbsp; &nbsp; &nbsp; id = Column(Integer, primary_key=True)&nbsp; &nbsp; class B(Base):&nbsp; &nbsp; &nbsp; &nbsp; __tablename__ = 'b'&nbsp; &nbsp; &nbsp; &nbsp; id = Column(Integer, primary_key=True)&nbsp; &nbsp; class C(Base):&nbsp; &nbsp; &nbsp; &nbsp; __tablename__ = 'c'&nbsp; &nbsp; &nbsp; &nbsp; a = Column(Integer, primary_key=True)&nbsp; &nbsp; &nbsp; &nbsp; b = Column(Integer, primary_key=True)&nbsp; &nbsp; Base.metadata.create_all(_sql_engine)&nbsp; &nbsp; a = [A(id=10),&nbsp; &nbsp; &nbsp; A(id=20)]&nbsp; &nbsp; c = [C(a=10, b=11), C(a=20, b=21)]&nbsp; &nbsp; b = [B(id=11)]&nbsp; &nbsp; session.add_all(a + c + b)&nbsp; &nbsp; session.commit()&nbsp; &nbsp; q = session.query(A).filter(&nbsp; &nbsp; &nbsp; &nbsp; A.id < literal(100),&nbsp; &nbsp; &nbsp; &nbsp; ~(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; session.query(C)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .filter(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A.id == C.a,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ~(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; session.query(B)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .filter(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B.id == C.b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ).exists().correlate(A,C)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ).exists()&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp; &nbsp; print(q.statement)&nbsp; &nbsp; print(len(q.all()))&nbsp; &nbsp; assert len(q.all()) == 1