from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id))
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
parent_cte = session.query(Parent).cte('parent_cte')
query = session.query(Child).filter(Child.parent_id.in_(parent_cte)).all()
gives the following query:
SELECT children.id AS children_id, children.parent_id AS children_parent_id
FROM children
WHERE children.parent_id IN (SELECT parents.id FROM parents)
But I need appropriate CTE usage:
WITH parent_cte AS (
SELECT parents.id FROM parents
)
SELECT children.id AS children_id, children.parent_id AS children_parent_id
FROM children
WHERE children.parent_id IN (SELECT parent_cte.id FROM parent_cte)
How can I use CTE to make query go this way?
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/89759c32-5cf7-4ad7-ad67-02cea67e6407%40googlegroups.com.
