SQLAlchemy convert outer join ORM query to Core
outer-join, python, sql, sqlalchemy
Solution
I finally managed to solved the problem. Instead of cascading `select_from`, additional joins need to be chained to the actual join. The above query would read:
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
)
)
)
Problem
I'm having problems with SQLAlchemy's `select_from` statement when using the core component. I try to construct an outer join query which currently looks like: ``` 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 ...) ``` According to the docs, the structure should look like this: ``` 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) ) ``` My problem is that I don't have a table1 object in this case, as the `select ...` part consists of columns and not a single table (see question marks in my query). I've tried using `n1.outerjoin(n1...`, but that caused an exception (`Exception: (ProgrammingError) table name "n1" specified more than once`). The above snippet is derived from a working session-based (ORM) query, which I try to convert (with limited success). ``` 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) ``` How do I go about converting this ORM query into a plain Core query? Update: I was able to narrow down the problem. It seems that a combination of two `select_from` calls causes the problem. ``` 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 ``` The above code causes the following exception: ``` 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' {} ``` If I was a bit more experienced in using SQLAlchemy, I would say this could be a bug...