I wonder if the filter could be set within the model.
e.g. something along these lines
mapper(User, users_table, properties={
'posts': dynamic_loader(Post). filter(Post.headline=='this is a post')
})
I am actually still looking for a nice solution/recipe to handle I18N
content in the database, so was wondering if I could do something like this.
class Country_T(Base):
__table__ = sa.Table(u'countries_t', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_t_id'),
primary_key=True, nullable=False),
sa.Column(u'lang_code5', sa.String(length=5,
convert_unicode=False), sa.ForeignKey(u'languages.code5')),
sa.Column(u'name', sa.String(length=50, convert_unicode=False)),
sa.Column(u'countries_b_id', sa.Integer(),
sa.ForeignKey(u'countries_b.id')),
)
class Country_B(Base):
__table__ = sa.Table(u'countries_b', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_b_id'),
primary_key=True, nullable=False),
sa.Column(u'iso2', sa.String(length=2, convert_unicode=False)),
sa.Column(u'iso3', sa.String(length=3, convert_unicode=False)),
)
country = sao.dynamic_loader('Country_T',
filter('Country_T.lang_code5', getCurrentUserLang))
"country" relation above should return the row from country_t which
corresponds to the user requested language.
Ideally I like to have a way to have it "fall back" to a default language.
Has anyone a recipe on how to do something like this? I am aware of
Karsten Hilbert's solution which is "gettext" based using stored
procedures, I like to find a solution which does not require stored
procedures.
Thanks in advance for any hints, tips or sample code.
Werner
class Country_B(Base):
__table__ = sa.Table(u'countries_b', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_b_id'),
primary_key=True, nullable=False),
sa.Column(u'iso2', sa.String(length=2, convert_unicode=False)),
sa.Column(u'iso3', sa.String(length=3, convert_unicode=False)),
)
country = sao.dynamic_loader('Country_T',
backref=sao.backref('countries_b', lazy='dynamic'))
ct = session.query(db.Country_B)
for x in ct:
try:
xy =
x.country.filter(db.Country_T.lang_code5==db.getCurrentUserLang()).one()
print 'pref: %s' % xy
print xy.name
except db.sao.exc.NoResultFound:
try:
print 'def: %s' %
x.country.filter(db.Country_T.lang_code5==db.getDefaultUserLang()).one()
except db.sao.exc.NoResultFound:
print 'no translation found'
Now, if I could put this try/except block into the mapper that would be
just perfect.
Is this possible? If not what else could be done?
Werner
have you considered http://www.sqlalchemy.org/docs/mappers.html#building-query-enabled-properties ?
>
> Werner
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
On 24/01/2010 15:28, Michael Bayer wrote:
...
> have you considered http://www.sqlalchemy.org/docs/mappers.html#building-query-enabled-properties ?
>
No - had not found that.
Needed a bit to figure it out. I am using declarative and ended up with
this.
Michael, thanks a lot, I think I am getting there, the following works
for me, maybe it is useful for others.
class Country_B(Base):
__table__ = sa.Table(u'countries_b', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_b_id'),
primary_key=True, nullable=False),
sa.Column(u'iso2', sa.String(length=2, convert_unicode=False)),
sa.Column(u'iso3', sa.String(length=3, convert_unicode=False)),
)
def _get_translation(self):
try:
x =
sao.object_session(self).query(Country_T).with_parent(self).filter(Country_T.lang_code5==getCurrentUserLang)
## print 'def: %s' % x
## print 'def: %s' % getCurrentUserLang()
return x.one()
except sao.exc.NoResultFound:
try:
x =
sao.object_session(self).query(Country_T).with_parent(self).filter(Country_T.lang_code5==getDefaultUserLang)
## print 'def: %s' % x
## print 'def: %s' % getDefaultUserLang()
return x.one()
except sao.exc.NoResultFound:
return 'no translation found'
country_t = sao.relation('Country_T', backref='country_b')
country = property(_get_translation)
Next thing is to make "_get_translation" reusable for different tables.
Werner
def _do_translation(bInstance, tTable, fCrit, cLang, dLang):
try:
x =
sao.object_session(bInstance).query(tTable).with_parent(bInstance).filter(fCrit==cLang)
return x.one()
except sao.exc.NoResultFound:
try:
x =
sao.object_session(bInstance).query(tTable).with_parent(bInstance).filter(fCrit==dLang)
return x.one()
except sao.exc.NoResultFound:
return 'no translation found'
class Country_B(Base):
__table__ = sa.Table(u'countries_b', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_b_id'),
primary_key=True, nullable=False),
..
)
def _get_translation(self):
return _do_translation(self, Country_T, Country_T.lang_code5,
getCurrentUserLang, getDefaultUserLang)
country_t = sao.relation('Country_T', backref='country_b')
country = property(_get_translation)
But would like to remove the "def _get_translation" and call directly
_do_translation, something like this:
country = property(_do_translation('Country_B', 'Country_T',
'lang_code5', getCurrentUserLang, getDefaultUserLang))
But can't figure out how I would then get at the instance of "Country_B"
within _do_translation.
As always tips or pointers to documentation are very appreciated.
Werner
Hi Werner,
You need to implement your own 'property'-like class that implements the
descriptor protocol. This page might give you some clues:
http://users.rcn.com/python/download/Descriptor.htm#descriptor-example
If you had a TranslationProperty class that looked like this:
class TranslationProperty(object):
def __get__(self, obj, objtype):
# Call _do_translation(obj, ...) here
Then your Country class can look like:
class Country_B(Base):
country_t = TranslationProperty()
When you access the country_t attribute, the __get__ method will be
called with your Country_B instance as the obj parameter, and the
Country_B class as the objtype parameter, which you can hopefully pass
on to the _do_translation function.
I hope that helps,
Simon
On 25/01/2010 15:18, King Simon-NFHD78 wrote:
...
> I hope that helps,
Yes, that did help a lot.
This is my custom property class.
class TranslationProperty(object):
"""Returns a query enabled property
"""
def __init__(self, tTable=None, fCol=None, cLang=None, dLang=None):
self.tTable = tTable
self.fCol = fCol
self.cLang = cLang
self.dLang = dLang
def __get__(self, obj, objtype):
# get the class containing the translations
tTable = globals()[self.tTable]
# get the column containing the translation code, e.g. "EN_en"
fCrit = getattr(tTable, self.fCol)
try:
return
sao.object_session(obj).query(tTable).with_parent(obj).filter(fCrit==self.cLang).one()
except sao.exc.NoResultFound:
try:
return
sao.object_session(obj).query(tTable).with_parent(obj).filter(fCrit==self.dLang).one()
except sao.exc.NoResultFound:
return 'no translation found'
Then this is my country table with columns which don't need translation:
class Country_B(Base):
__table__ = sa.Table(u'countries_b', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_b_id'),
primary_key=True, nullable=False),
sa.Column(u'iso2', sa.String(length=2, convert_unicode=False)),
sa.Column(u'iso3', sa.String(length=3, convert_unicode=False)),
...
)
country_t = sao.relation('Country_T', backref='country_b')
country = TranslationProperty('Country_T', 'lang_code5',
getCurrentUserLang, getDefaultUserLang)
The table with translation columns:
class Country_T(Base):
__table__ = sa.Table(u'countries_t', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_t_id'),
primary_key=True, nullable=False),
sa.Column(u'lang_code5', sa.String(length=5,
convert_unicode=False), sa.ForeignKey(u'languages.code5')),
sa.Column(u'name', sa.String(length=50, convert_unicode=False)),
....
sa.Column(u'countries_b_id', sa.Integer(),
sa.ForeignKey(u'countries_b.id')),
)
Then using it like this:
db.setCurrentUserLang('FR_fr')
ct = session.query(db.Country_B)
for x in ct:
print x
print x.country
print x.country.name
db.setCurrentUserLang('DE_de')
for x in ct:
print x
print x.country
print x.country.name
Resulting in this:
Country_B(id=2, iso2=u'FR', iso3=u'FRA')
Country_T(countries_b_id=2, id=3, lang_code5=u'EN_en', name=u'France')
France
Country_B(id=2, iso2=u'FR', iso3=u'FRA')
Country_T(countries_b_id=2, id=4, lang_code5=u'DE_de', name=u'Frankreich')
Frankreich
The test db does not contain an "FR_fr" translation, and the default
language is set to "EN_en".
Now I just need to clean up the code, document it a bit and add better
error handling.
Simon and Michael, thanks again for helping me with all this.
Hope above might be useful for others.
Werner