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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAPy-swN5KF1tUj29VBY6Dtyjq%3DtV%3D7Uzv71VkpcuSvB8a5Dz9g%40mail.gmail.com.
