sa.py

### Content

def includeme(config):

    orm.mapper(
        ContentTranslation, tables['content_translation'],
        properties={
            'language': orm.relationship(
                Language,
                lazy='joined',
                innerjoin=True,
                uselist=False
            ),

            'content': orm.relationship(
                Content,
                innerjoin=True,
                uselist=False,
#                back_populates='translations',
            ),
        }
    )

    content_mapper = orm.mapper(
        Content, tables['content'],
        polymorphic_on=tables['content'].c.content_type_id,
        properties={
            'type': orm.relationship(
                ContentType,
                lazy='joined',
                innerjoin=True
            ),
        })

    j = orm.join(
        ContentTranslation, Content,
        ContentTranslation.content_id == Content.id
    ).join(
        ContentType,
        ContentType.id == Content.content_type_id
    )

    foo_mapper = orm.mapper(
        ContentTranslation, j,
        polymorphic_on=j.c.content_type_id,
        non_primary=True, properties={
            'id': orm.column_property(
                j.c.content_id
            ),

            'content_type_id': orm.column_property(
                j.c.content_content_type_id,
                j.c.content_type_id
            ),

            'description': orm.column_property(
                j.c.content_translation_description
            ),

            'content_type_description': orm.column_property(
                j.c.content_type_description
            ),
        }
    )

    content_mapper.add_property(
      'translations', orm.relationship(
        foo_mapper,
        primaryjoin=ContentTranslation.content_id==foo_mapper.c.id,
        innerjoin=True,
        collection_class=attribute_mapped_collection('language_id')
      )
    )

### Document

def includeme(config):
    tables = config.registry['metadata'].tables

    orm.mapper(
        DocumentTranslation, tables['document_translation'],
        inherits=ContentTranslation,
        polymorphic_identity=get_type_id(config, 'document'),
        polymorphic_load='inline'
    )

    orm.mapper(
        Document, tables['document'], inherits=Content,
        polymorphic_identity=get_type_id(config, 'document'),
    )

### TEST

>>> a=request.dbsession.query(Document).get(100)
2018-04-11 12:07:19,523 INFO  [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit)
2018-04-11 12:07:19,526 INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT content.id AS content_id, content.added AS content_added, content.updated AS content_updated, content.effective AS content_effective, content.expiration AS content_expiration, content.exclude_nav AS content_exclude_nav, content.weight AS content_weight, content.content_type_id AS content_content_type_id, content.container_id AS content_container_id, content.owner_id AS content_owner_id, content.state_id AS content_state_id, content.is_fts AS content_is_fts, content.props AS content_props, document.content_id AS document_content_id, account_1.id AS account_1_id, account_1.login AS account_1_login, account_1.password AS account_1_password, account_1.name AS account_1_name, account_1.first_name AS account_1_first_name, account_1.email AS account_1_email, account_1.enabled AS account_1_enabled, account_1.created AS account_1_created, account_1.picture AS account_1_picture, account_1.lost_token AS account_1_lost_token, content_type_1.id AS content_type_1_id, content_type_1.name AS content_type_1_name, content_type_1.description AS content_type_1_description, content_type_1.icons AS content_type_1_icons, state_1.id AS state_1_id, state_1.name AS state_1_name 
FROM content JOIN document ON content.id = document.content_id JOIN account AS account_1 ON account_1.id = content.owner_id JOIN content_type AS content_type_1 ON content_type_1.id = content.content_type_id JOIN state AS state_1 ON state_1.id = content.state_id 
WHERE content.id = %(param_1)s
2018-04-11 12:07:19,526 INFO  [sqlalchemy.engine.base.Engine][MainThread] {'param_1': 100}

## XXX the JOIN on document_translation is missing, and it returns a ContentTranslation instead of a DocumentTranslation
>>> a.translations
2018-04-11 12:07:24,419 INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT content_translation.description AS content_translation_description, content.content_type_id AS content_content_type_id, content_type.id AS content_type_id, content_type.description AS content_type_description, content.id AS content_id, content_translation.language_id AS content_translation_language_id, content_translation.content_id AS content_translation_content_id, content_translation.title AS content_translation_title, content_translation.fts AS content_translation_fts, content.added AS content_added, content.updated AS content_updated, content.effective AS content_effective, content.expiration AS content_expiration, content.exclude_nav AS content_exclude_nav, content.weight AS content_weight, content.container_id AS content_container_id, content.owner_id AS content_owner_id, content.state_id AS content_state_id, content.is_fts AS content_is_fts, content.props AS content_props, content_type.name AS content_type_name, content_type.icons AS content_type_icons 
FROM content_translation JOIN content ON content_translation.content_id = content.id JOIN content_type ON content_type.id = content.content_type_id 
WHERE content_translation.content_id = %(param_1)s
2018-04-11 12:07:24,419 INFO  [sqlalchemy.engine.base.Engine][MainThread] {'param_1': 100}
{'en': <amnesia.modules.content.model.ContentTranslation object at 0x80bc0b358>}