hey there - replacement_traverse is a mostly internal API that is very fickle and is not guaranteed to work in generic situations; it's tuned very specifically to cases that the ORM needs internally and one of the architectural trends in SQLAlchemy is to try to reduce dependence on it.
Can we step back and try to look at what actual problem you're trying to solve? you would be much better off building up the SQL constructs that you want up front, or if some variability is needed, building coarse-grained builder functions that apply these varieties when the construct is built up front. On Thu, Nov 25, 2021, at 4:14 PM, andrew....@gmx.co.uk wrote: > *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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c8559803-ba84-447d-9441-c95b767b488dn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c8559803-ba84-447d-9441-c95b767b488dn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/89c30527-0161-434f-b1ad-69ee21558fd2%40www.fastmail.com.
