python - SQLAlchemy 将外连接 ORM 查询转换为核心

Question

我在使用 SQLAlchemy 的 select_from 时遇到问题使用核心组件时的声明。我尝试构建一个当前看起来像的外部连接查询:

query = select([b1.c.id, b1.c.num, n1.c.name, n1.c.num, ...]
        ).where(and_(
            ... some conditions ...
        )
    ).select_from(
        ???.outerjoin(
        n1,
        and_(
            ... some conditions ...
        )
    ).select_from(... more outer joins similar to the above ...)

根据文档，结构应该是这样的:

table1 = table('t1', column('a'))
table2 = table('t2', column('b'))
s = select([table1.c.a]).\
    select_from(
        table1.join(table2, table1.c.a==table2.c.b)
    )

我的问题是在这种情况下我没有 table1 对象，因为 select ...部分由列而不是单个表组成(请参阅我的查询中的问号)。我试过使用 n1.outerjoin(n1... , 但这导致了异常 ( Exception: (ProgrammingError) table name "n1" specified more than once )。

上面的代码片段源自一个基于工作 session 的 (ORM) 查询，我尝试对其进行转换(但取得的成功有限)。

b = Table('b', metadata, 
    Column('id', Integer, Sequence('seq_b_id')),
    Column('num', Integer, nullable=False),
    Column('active', Boolean, default=False),
)
n = Table('n', metadata, 
    Column('b_id', Integer, nullable=False),
    Column('num', Integer, nullable=False),
    Column('active', Boolean, default=False),
)
p = Table('p', metadata, 
    Column('b_id', Integer, nullable=False),
    Column('num', Integer, nullable=False),
    Column('active', Boolean, default=False),
)

n1 = aliased(n, name='n1')
n2 = aliased(n, name='n2')
b1 = aliased(b, name='b1')
b2 = aliased(b, name='b2')
p1 = aliased(p, name='p1')
p2 = aliased(p, name='p2')

result = sess.query(b1.id, b1.num, n1.c.name, n1.c.num, p1.par, p1.num).filter(
        b1.active==False,
        b1.num==sess.query(func.max(b2.num)).filter(
            b2.id==b1.id
        )
    ).outerjoin(
        n1, 
        and_(
            n1.c.b_id==b1.id,
            n1.c.num<=num,
            n1.c.active==False,
            n1.c.num==sess.query(func.max(n2.num)).filter(
                n2.id==n1.c.id
            )
        )
    ).outerjoin(
        p1,
        and_(
            p1.b_id==b1.id,
            p1.num<=num,
            p1.active==False,
            p1.num==sess.query(func.max(p2.num)).filter(
                p2.id==p1.id
            )
        )
    ).order_by(b1.id)

如何将此 ORM 查询转换为普通的 Core 查询？ 更新:

我能够缩小问题范围。好像是两个 select_from的组合调用导致问题。

customer = Table('customer', metadata,
    Column('id', Integer),
    Column('name', String(50)),
)
order = Table('order', metadata,
    Column('id', Integer),
    Column('customer_id', Integer),
    Column('order_num', Integer),
)
address = Table('address', metadata,
    Column('id', Integer),
    Column('customer_id', Integer),        
    Column('city', String(50)),
)
metadata.create_all(db)

customer1 = aliased(customer, name='customer1')
order1 = aliased(order, name='order1')
address1 = aliased(address, name='address1')

columns = [
    customer1.c.id, customer.c.name,
    order1.c.id, order1.c.order_num, 
    address1.c.id, address1.c.city
]
query = select(columns)
query = query.select_from(
    customer1.outerjoin(
        order1,
        and_(
            order1.c.customer_id==customer1.c.id,
        )
    )
)
query = query.select_from(
    customer1.outerjoin(
        address1,
        and_(
            customer1.c.id==address1.c.customer_id
        )
    )
)    
result = connection.execute(query)
for r in result.fetchall():
    print r

上述代码会导致以下异常:

ProgrammingError: (ProgrammingError) table name "customer1" specified more than once
 'SELECT customer1.id, customer.name, order1.id, order1.order_num, address1.id, address1.city \nFROM customer, customer AS customer1 LEFT OUTER JOIN "order" AS order1 ON order1.customer_id = customer1.id, customer AS customer1 LEFT OUTER JOIN address AS address1 ON customer1.id = address1.customer_id' {}

如果我在使用 SQLAlchemy 方面更有经验，我会说这可能是一个错误...

Answer 1

我终于设法解决了这个问题。不是级联 select_from，而是需要将其他连接链接到实际连接。上面的查询将显示为:

query = select(columns)
query = query.select_from(
    customer1.outerjoin(
        order1,
        and_(
            order1.c.customer_id==customer1.c.id,
        )
    ).outerjoin(
        address1,
        and_(
            customer1.c.id==address1.c.customer_id
        )
    )
)