there's a lot there, here's a proof of concept:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
sess = Session()
cte_1 = sess.query(A).cte('c1')
cte_2 = sess.query(A).cte('c2')
stmt = union(cte_1.select(), cte_2.select())
print sess.query(A).select_entity_from(stmt)
if we want to filter, making an alias() out of stmt then using that works:
stmt = union(cte_1.select(), cte_2.select()).alias()
print sess.query(A).select_entity_from(stmt).filter(stmt.c.data == 'hi')
output:
WITH c1 AS
(SELECT a.id AS id, a.data AS data
FROM a),
c2 AS
(SELECT a.id AS id, a.data AS data
FROM a)
SELECT anon_1.id AS anon_1_id, anon_1.data AS anon_1_data
FROM (SELECT c1.id AS id, c1.data AS data
FROM c1 UNION SELECT c2.id AS id, c2.data AS data
FROM c2) AS anon_1
WHERE anon_1.data = :data_1
On Sep 25, 2014, at 9:12 PM, Jonathan Vanasco <[email protected]> wrote:
> This is the test case I've been working on today.
>
> https://gist.github.com/jvanasco/9be1f528526e496fc751
>
> The target sql looks crappy, because it's just an example.
>
> the various issues have been:
>
> * if i can build a query without an Exception:
> ** I duplicate the query within a subqueries
> ** I build it in a way that I don't have addressable columns (for
> sorting/joins/etc)
>
> * if i raise an exception (more often), it's because I'm not able to turn a
> union/cte into the correct type of object (which has addressable columns, etc)
>
>
> --
> 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.
--
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.
