Re: [sqlalchemy] Change a select clause + add a join automatically
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 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
> 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
>>
>> .
>>
> --
> 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
>
> .
>
--
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
Re: [sqlalchemy] Change a select clause + add a join automatically
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
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
>
> .
>
--
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.
[sqlalchemy] Change a select clause + add a join automatically
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.
