from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, joinedload
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property, Comparator
from sqlalchemy.sql import select

class BaseCFG(object):
    @declared_attr
    def __tablename__(cls):  # @NoSelf
        return cls.__name__.lower()

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(cls=BaseCFG)
Session = sessionmaker(bind=engine)
session = Session()


def filter_locale(locale):
    def transform(query):
        return query.join(ItemI18N).filter(ItemI18N.lang_short == "en").options(joinedload("i18ns"))
    return transform

class Item(Base):
    id = Column(Integer, primary_key=True)
    data = Column(String)
    i18ns = relationship("ItemI18N", backref="item", cascade="all, delete-orphan", lazy="joined")

    def name(self, locale):
        for i18n in self.i18ns:
            if i18n.language.short == locale:
                return i18n.name

class ItemI18N(Base):
    lang_id = Column(Integer, ForeignKey('language.id'), primary_key=True)
    item_id = Column(Integer, ForeignKey('item.id'), primary_key=True)
    name = Column(String)
    language = relationship("Language", lazy="joined")


class Language(Base):
    id = Column(Integer, primary_key=True)
    short = Column(String)

Base.metadata.create_all(engine)

# Init data
en = Language(short="en")
de = Language(short="de")
session.add_all([en, de])
item = Item(data="item")
item_en = ItemI18N(name="English Name")
item_en.language = en
item.i18ns.append(item_en)
item_de = ItemI18N(name="German Name")
item_de.language = de
item.i18ns.append(item_de)
session.add(item)
session.flush()
session.expunge(item)

# Use it as a list
#item = session.query(Item).first()
#print item.data, [i18n.name for i18n in item.i18ns if i18n.lang_short == "en"][0]

# Get the name separately
#item, name = session.query(Item, ItemI18N).join(ItemI18N).filter(ItemI18N.lang_short == "en").first()
#print item.data, name

# Hybrid
#[item] = session.query(Item).join(ItemI18N).filter(ItemI18N.lang_short == "en").options(joinedload("i18ns")).all()
#print item.data, item.name("en")

#[item] = session.query(Item).with_transformation(filter_locale("en")).all()
#print item.data, item.name("en")

[item] = session.query(Item).all()
print item.data, item.name("en")

#name = session.query(Item.name).join(Item).filter(ItemI18N.lang_short == "en").all()
#print name