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 On Tue, Apr 11, 2023 at 5:13 PM Nishant Varma <nishant.va...@gmail.com> wrote: > 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 > > -- > 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/CAPy-swN5KF1tUj29VBY6Dtyjq%3DtV%3D7Uzv71VkpcuSvB8a5Dz9g%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAPy-swN5KF1tUj29VBY6Dtyjq%3DtV%3D7Uzv71VkpcuSvB8a5Dz9g%40mail.gmail.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/CAFHwexfb1OQ09OEUqhRUB8vW0v57OS1n_M%2B7ESRh2RFB8gFSTw%40mail.gmail.com.
