On May 19, 2014, at 9:12 PM, gbr <[email protected]> wrote: > 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')
the error here is that you're trying to add things to the SELECT in terms of the SELECT itself, which would mean that you want to SELECT FROM that SELECT. Consider this statement: SELECT a, b FROM t1 if we want to add a WHERE, we want it in terms of "a, b", columns from "t1": SELECT a, b FROM t1 WHERE a > b that is, when we say "WHERE a > b", the parent table of a and b is t1. if on the other hand, we wanted our SELECT to be a subquery, then we can imagine it like this: (SELECT a, b FROM t1) AS subq above, "subq" also has columns named "a" and "b". These correspond to the columns t1.a and t1.b, but they are not the same. if we want to SELECT from subq and specify a WHERE, it is apparent that we should talk about "subq" and the columns there: SELECT subq.a, subq.b FROM (SELECT a, b FROM t1) AS subq WHERE subq.a > subq.b on the other hand, if we wanted to select from subq and t1 at the same time, we refer to these columns distinctly as related to their FROM, or what SQLAlchemy calls the "selectable": SELECT subq.a, subq.b, t1.a, t1.b FROM t1, (SELECT a, b FROM t1) AS subq WHERE subq.a > subq.b AND t1.a > t1.b the point is, when specifying columns in a WHERE clause or anything else, it is essential that the correct parent selectable is used. From a WHERE column or similar, the parent selectable of that column will be added to the FROM list of the SELECT. In this case, to add a WHERE clause to "sel" in terms of "product", we must refer to those columns directly: sel2 = sel.where(product_table.c.name == 'water') I'd recommend the Core Tutorial at http://docs.sqlalchemy.org/en/latest/core/tutorial.html which has lots and lots of examples of building up SELECT objects. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
