The above example is just a simplified version of the actual code. In fact, the construction of `sel` is much more difficult and `product_table` is actually a `select` made from multiple joins which only really exists within the function that creates `sel`. One wouldn't be able to reference it by `product_table.c.name`.
On Tuesday, May 20, 2014 11:25:59 AM UTC+10, rpkelly wrote: > > Is there some issue with doing: sel2 = sel.where(product_table.c.name > == "water") ? > > If you really have no reference to the product_table, I think you can > hack it with sel._raw_columns to try and find the column you want to > filter to. > > -Ryan Kelly > > On Mon, May 19, 2014 at 9:12 PM, gbr <[email protected] <javascript:>> > 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') > >>>> # 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 [email protected] <javascript:>. > > To post to this group, send email to > > [email protected]<javascript:>. > > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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.
