Change a select clause + add a join automatically

[Nishant Varma]

Hello,

I have this schema:

class Question(Base):
    __tablename__ = "question"
    idn = Column(Integer, primary_key=True)
    text = Column("text", String)

    translations = relationship("Translation", backref="question")

class Translation(Base):
    __tablename__ = "translation"
    idn = Column(Integer, primary_key=True)
    qid = Column(Integer, ForeignKey("question.idn"))
    lang = Column(String)
    text = Column(String)

I want to automatically join Question with a single Translation (lang filter) when the real query happens. Language will be supplied runtime. I tried to think of this as a property, but that doesn't seem to work.

Currently, I am using a  simple function to achieve this:

def translate(query, lang):
    cols = list(query.selectable.selected_columns)
    index = next(
        (
            i
            for i, c in enumerate(cols)
            if c.name == "text" and c.table.name == "question"
        ),
        None,
    )
    text = case(
        [(Translation.text.is_not(None), Translation.text)],
        else_=Question.text,
    )
    cols[index] = text.label("text")
    query._set_entities(cols)  # noqa:W0212
    query = query.outerjoin(
        Translation,
        and_(Question.idn == Translation.qid, Translation.lang == lang),
    )
    return query

Usage: query = translate(query, "it")

1. Is there a good place to hook this so that it's baked into the Question table? The tricky part is when and where to pass "it". I guess that can be only done during session.query. If so, how to do that? (I know about bind-params, so I am thinking that will be an option here.)

2. Is there a better approach to solve this problem? I tried looking up examples, but didn't find one that fits my purpose (should be simple, should be at a single place etc.).

SQLAlchemy 1.3 or 1.4

Thank you for help in advance,

Nishant

[Simon King]

I think this is the intended use for the do_orm_execute event and the with_loader_criteria query option:

https://docs.sqlalchemy.org/en/14/orm/session_events.html#do-orm-execute-global-criteria

https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.with_loader_criteria

You ought to be able to use the event hook to add a loader criteria option targeting the appropriate translation.

Simon

--
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/CAPy-swN5KF1tUj29VBY6Dtyjq%3DtV%3D7Uzv71VkpcuSvB8a5Dz9g%40mail.gmail.com.

[Nishant Varma]

Thank you! It is indeed a query-interception requirement, so that looks appropriate. I will have a deeper look on how it can be used.

Rgds

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfb1OQ09OEUqhRUB8vW0v57OS1n_M%2B7ESRh2RFB8gFSTw%40mail.gmail.com.