Hi all,
I'm writing a new application against an existing DB (too bad, it's MySQL :-[)
that may contain information in multiple languages. The original author
structured the tables like the following:
CREATE TABLE products (
ID integer,
CODE varchar(20),
/* "short" description */
IT varchar(100),
EN varchar(100),
FR varchar(100),
...
/* "long" description */
IT_NOTE varchar(500),
EN_NOTE varchar(500),
FR_NOTE varchar(500),
...
)
The user of the application has a "preferred language", that can come either
from his own "profile" or from an external source (for example, the browser
locale setting when the user is anonymous).
I would like to be able to express my SA queries with that in mind, so for
example end up with
SELECT id, code, it as description, it_note as summary
FROM products
when the "preferred language" is "IT" and with
SELECT id, code, en as description, en_note as summary
FROM products
when it is set to "EN".
For now, I settled to build my query using an helper function similar to the
following:
def localized_field(cols, field_pattern="{lang}"):
known_langs = ('it', 'en', ...)
lparam = sa.bindparam('lang')
return sa.case([(lparam == lang.upper(),
cols[field_pattern.format(lang=lang)])
for lang in known_langs],
else_='Unsupported user lang: '+lparam)
that lets me write
pc = products.c
query = sqlalchemy.select([pc.id,
localized_field(pc).label('description'),
localized_field(pc,
'{lang}_note').label('summary)])
Although it works, it is obviously suboptimal, in particular when I'm going to
further refine the query, for example adding a filter on one of those
fields...
So I wonder if there is any clever trick that SA could support to avoid that
"long" CASE statement for each field. Assuming for example I could "mark" in
some way those columns, I would have a function that, traversing the query
structure replaces all instances of marked fields with the right "LANG_xxx"
one.
Another possibility I can see is taking advantage of custom constructs or
compilation extension, that would probably be the cleanest approach, if they
could be "parametrized" in some way injecting the "preferred language" when
they get applied by the SA compilation step.
Do you have any hints on how to get there, or other approaches I missed?
Thanks a lot,
ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
[email protected] | -- Fortunato Depero, 1929.
--
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 post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
