sqlalchemy mapping related model columns

sqlalchemy_mapping_related_model_columns.py

#https://stackoverflow.com/questions/19780178/sqlalchemy-hybrid-expression-with-relationship

# 1 association proxy: http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html
from sqlalchemy.ext.associationproxy import association_proxy

class NodeVariable(Variable):
    __tablename__ = 'nodevariables'
    id = db.Column(db.Integer, db.ForeignKey('variables.id', name='fk_nodevariables_variables_id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity': VariableKind.NodeVariable,
    }
    node_id = db.Column(db.Integer, db.ForeignKey('nodes.id', name='fk_nodevariables_nodes_id'))
    node = db.relationship('Node', backref=db.backref('variables', lazy='dynamic'))

    orchard_id = association_proxy('node', 'orchard_id')


# 2 @hybrid_property + set expression in class
from sqlalchemy.ext.hybrid import hybrid_property

class NodeVariable(Variable):
    __tablename__ = 'nodevariables'
    id = db.Column(db.Integer, db.ForeignKey('variables.id', name='fk_nodevariables_variables_id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity': VariableKind.NodeVariable,
    }
    node_id = db.Column(db.Integer, db.ForeignKey('nodes.id', name='fk_nodevariables_nodes_id'))
    node = db.relationship('Node', backref=db.backref('variables', lazy='dynamic'))
    
    @hybrid_property
    def orchard_id(self):
        if self.kind == VariableKind.NodeVariable:
            return self.node.orchard_id
    
    @orchard_id.expression
    def orchard_id(cls):
        from .models import Node
        if cls.__name__ == NodeVariable.__name__:
            return db.select([Node.orchard_id]).where(cls.node_id == Node.id).as_scalar()

# 3 @hybrid_property + set comparator with a transformer of the query
# TODO