That makes sense. I was assuming that the WHERE clause with reference to
expr.columns would involve some subquery magic, but granted, this is not
easy to do in the general case (especially considering that with dotted
composition the evaluation is done backwards ;-)).
Thanks for your proposition; actually I don't fancy too much hacking into
the framework. I'll give a shot at CTEs, which I had overlooked in the
docs. They're a good candidate as they give a "procedural" taste to SQL and
this is exactly what I'm trying to do :-)
Thanks a lot for the quick answer!
Thomas
Le 2 janv. 2017 6:07 PM, "mike bayer" <mike...@zzzcomputing.com> a écrit :
On 01/02/2017 11:14 AM, tvial wrote:
> Hi and a happy new year to all :)
>
> I am having trouble applying a WHERE clause to a chained SELECT
> expression, by referencing the columns of the expression itself. Hard to
> put in words, it's better to give an example:
>
> |
> fromsqlalchemy importcreate_engine
> fromsqlalchemy importTable,Column,Integer,String,MetaData,ForeignKey
>
> engine =create_engine('sqlite:///:memory:')
>
> connection =engine.connect()
>
> metadata =MetaData()
>
> parts =Table('parts',metadata,
> Column('id',Integer,primary_key=True),
> Column('name',String),
> Column('price',Integer),
> )
>
> metadata.create_all(engine)
>
> expr =parts.select()
> printexpr
> # SELECT parts.id, parts.name, parts.price
> # FROM parts
> #
> # -> this is expected
>
> printexpr.c
> # ['id', 'name', 'price']
> #
> # -> OK, I should be able to refer the columns of the result
>
> printexpr.where(expr.c.id ==1)
> # SELECT parts.id, parts.name, parts.price
> # FROM parts, (SELECT parts.id AS id, parts.name AS name, parts.price AS
> price
> # FROM parts)
> # WHERE id = :id_1
> #
> # -> Uh oh, the table is joined to itself, which is wrong, and the name
> `id`
> # from the WHERE clause is ambiguous
> |
>
>
>
> In this simple case, of course it would be better to use:
> |
> printexpr.where(parts.c.id ==1)
> |
> and indeed it works as expected.
>
> But in practice I want to be able to build complex expression, and
> introduce filters anywhere up in the chain. I suspect I'm demanding too
> much -- on the other hand, the expr object exposes the name of its
> columns, so it's worth trying. Am I missing something?
>
the expr object exposes the names of its columns as the "outer" columns.
that is, if you have "SELECT x, y FROM table", that "selectable" exposes
"x" and "y" for subquery use, e.g. "SELECT x, y FROM (SELECT x, y FROM
table) AS foo".
For modifying the SELECT that you have, WHERE criteria is added in terms of
the selectables which that selectable is itself against. In this case,
the "parts" table.
Your concern about building up the chain raises the question, why would "up
the chain" know about "id" but not about "parts" ? These are both
arbitrary names. Building up the chain also usually means you need to add
joins and other things and you definitely need to know about the tables and
such being joined to or from. I can hack you up an easy way to get "
parts.id" out of "your_select / id" but it's not going to work for any
non-trivial case, like if your select is against two tables that both have
"id" and things like that.
> Thanks!
>
> Thomas
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
>
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
--- You received this message because you are subscribed to a topic in the
Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/sqlalchemy/rnrJrdQxkeg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
