I'm trying to modify a select statement which is fairly complex and which is created in a function that I don't want to modify. It is returned from this function and I'd like to add more WHERE clauses by just referencing the returned select statement. How can I do this without causing SQLA wrapping the returned select statement in another select?
See code below for demonstration. `sel` is the complex select statement (in my application returned from the function) and by `sel2 = sel.where()` I try to add another clause. >>> from sqlalchemy import * >>> metadata = MetaData() >>> product_table = Table('product', metadata, Column('id', Integer), Column('name', String(32))) >>> sel = select(columns=[product_table.c.id.label('product_id'), product_table.c.name.label('product_name'), order_table.c.id.label('order_id'), order_table.c.name.label('order_name')], from_obj=product_table.join(order_table, order_table.c.product_id==product_table.c.id)) >>> # Fine >>> print sel SELECT product.id AS product_id, product.name AS product_name, "order".id AS order_id, "order".name AS order_name FROM product JOIN "order" ON "order".product_id = product.id >>> # Trying to add a where condition to sel >>> sel2 = sel.where(sel.c.product_name=='water') >>> # Which unfortunately wraps the select in another select. Any way of adding the WHERE to `sel` post construction of `select()`? >>> print sel2 SELECT product.id AS product_id, product.name AS product_name, "order".id AS order_id, "order".name AS order_name FROM (SELECT product.id AS product_id, product.name AS product_name, "order".id AS order_id, "order".name AS order_name FROM product JOIN "order" ON "order".product_id = product.id), product JOIN "order" ON "order".product_id = product.id WHERE product_name = :product_name_1 >>> # I would have expected: >>> SELECT product.id AS product_id, product.name AS product_name, "order".id AS order_id, "order".name AS order_name FROM product JOIN "order" ON "order".product_id = product.id WHERE product_name = :product_name_1 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.