*Hello! I'm trying to replace a joined table in a join statement with a
subquery with WHERE clause added using **replacement_traverse()
<https://docs.sqlalchemy.org/en/14/core/visitors.html#sqlalchemy.sql.visitors.replacement_traverse>
utility**.
And I've managed to achieve a replacement itself, but I've stumbled upon
the problem of how to augment the join condition, as it stays unchanged
which makes the whole statement invalid. *
*I would appreciate any help.*
*Here is the test script that I use:*
*from sqlalchemy import Column, Text, Integer*
*from sqlalchemy.ext.declarative import declarative_base*
*from sqlalchemy.future import select*
*from sqlalchemy.orm import aliased*
*from sqlalchemy.sql.visitors import replacement_traverse*
*Base = declarative_base()*
*class Foo(Base):*
* __tablename__ = 'foo'*
* id = Column(Integer, primary_key=True)*
* name = Column(Text)*
* def __repr__(self):*
* return f'Foo(name={self.name})'*
*class Bar(Base):*
* __tablename__ = 'bar'*
* id = Column(Integer, primary_key=True)*
* name = Column(Text)*
* foo_id = Column(Integer)*
* def __repr__(self):*
* return f'Bar(name={self.name})'*
*class Baz(Base):*
* __tablename__ = 'baz'*
* id = Column(Integer, primary_key=True)*
* name = Column(Text)*
* foo_id = Column(Integer)*
* def __repr__(self):*
* return f'Baz(name={self.name})'*
*baz_alias = select(Baz.__table__).alias()*
*statement = select(Foo).join(baz_alias, Foo.id == baz_alias.c.foo_id)*
*def visitor(element):*
* # print(getattr(element, 'name', None), element is baz_alias)*
* if hasattr(element, 'name') and element.name == baz_alias.name:*
* print('Replacement happened!')*
* return select(Baz).filter(Baz.name == 'abc').alias()*
*# print(statement)*
*print(replacement_traverse(statement, {}, visitor))*
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c8559803-ba84-447d-9441-c95b767b488dn%40googlegroups.com.
