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 On Thu, Apr 20, 2023, 3:08 PM Simon King <si...@simonking.org.uk> wrote: > 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 > <https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfb1OQ09OEUqhRUB8vW0v57OS1n_M%2B7ESRh2RFB8gFSTw%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/CAPy-swNv8zuhVG-JZPn9fPPYVm2hxv2pxoWzKOt6ODf6YrV_4g%40mail.gmail.com.