Using replacement_traverse() to augment a join

[andrew....@gmx.co.uk]

Hello! I'm trying to replace a joined table in a join statement with a subquery with WHERE clause added using 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))

[Mike Bayer]

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.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c8559803-ba84-447d-9441-c95b767b488dn%40googlegroups.com.