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 <doubl...@directbox.com> 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 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.
--
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.
