don't do the alias and use query.from_statement()
On Thu, Apr 12, 2018 at 6:30 PM, Jonathan Vanasco <jvana...@gmail.com> wrote:
> I have a complicated recursive CTE that exists as text()
>
> _complex_sql_ = sqlalchemy.text("""WITH RECURSIVE _foos AS (
> SELECT id
> FROM foo
> WHERE (id = :id_start AND ...)
> UNION
> SELECT f.id
> FROM foo f
> INNER JOIN _foos _f ON _f.id = f.id
> )
> SELECT DISTINCT id FROM _foos""")
>
> I'd like to make this a selectable element that can be joined to ORM
> classes.
>
> The only way I can seem to move forward is with soemthing like this...
>
> stmt =
> sqlalchemy.text(_complex_sql_).bindparams(id_start=1).columns(sqlalchemy.sql.column('id',
> sqlalchemy.Integer))
>
> that wraps it in "SELECT id AS id FROM (_complex_sql_)" and fails because
> the subquery needs an alias
>
>
> stmt =
> sqlalchemy.text(_complex_sql_).bindparams(id_start=1).columns(sqlalchemy.sql.column('id',
> sqlalchemy.Integer)).alias(wrapped)
>
> that wraps it in "SELECT id AS id FROM (_complex_sql_) AS wrapped" and
> works
>
> is there any way to 'unwrap' that without the `SELECT id AS id FROM` stuff
> and just run the raw query?
>
> that form is throwing off one of the settings in the query planner, and it's
> a real pain to work on two versions of this query at once.
>
>
>
> --
> 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.
--
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.
