from sqlalchemy import *
db = create_engine( 'sqlite:///:memory:')
meta = BoundMetaData( db)
#meta.engine.echo=True

class Base2str( object):
    def __str__( me):
        r = me.__class__.__name__ +'/id'+ str(me.id) +'('
        for k in me.props:
            v = getattr( me, k, '<notset>')
            if isinstance( v, Base2str):
                v = '>' + v.__class__.__name__ +'/id'+ str( v.id)
            r+= ' '+k+'='+str(v)
        return r+' )'


#====generated SA set-up
table_Employee = Table( 'Employee', meta,
    Column( 'age',   type= Integer, ),
    Column( 'name',   type= String, ),
    Column( 'db_id',   primary_key= True,   type= Integer, ),
    Column( 'atype',   type= String, ),
)
table_Engineer = Table( 'Engineer', meta,
    Column( 'machine',   type= String, ),
    Column( 'helper_id', Integer, ForeignKey( 'Employee.db_id', ), ),
    Column( 'db_id', Integer, ForeignKey( 'Employee.db_id', ),   primary_key= True, ),
)

meta.create_all()

class Employee( Base2str):
    props = ['db_id', 'age', 'name']
class Engineer( Employee):
    props = ['db_id', 'machine', 'age', 'name', 'helper']

pu_employee = polymorphic_union( {
                'Employee': table_Employee.select( table_Employee.c.atype == 'Employee', ).alias( 'bz4Employee' ),
                'Engineer': join( table_Employee, table_Engineer, table_Engineer.c.db_id == table_Employee.c.db_id, ),
                }, None, 'pu_employee', ) #tableinh
mapper_Employee = mapper( Employee, table_Employee,
            polymorphic_identity= 'Employee',
            polymorphic_on= pu_employee.c.atype,
            select_table= pu_employee,
            )

mapper_Engineer = mapper( Engineer, table_Engineer,
            inherit_condition= table_Engineer.c.db_id == table_Employee.c.db_id,
            inherits= mapper_Employee,
            polymorphic_identity= 'Engineer',
            )
mapper_Engineer.add_property( 'helper', relation( Employee,
            foreignkey= table_Engineer.c.helper_id,
            lazy= True,
            primaryjoin= table_Engineer.c.helper_id == table_Employee.c.db_id,
            remote_side= table_Employee.c.db_id,
            uselist= False,
            ) )

#========= eo generated SA set-up

a = Employee()
a.name = 'anna'
a.age = 30

e = Engineer()
e.name = 'ben'
e.machine = 'fast'
e.helper = a

session = create_session()
session.save(a)
session.save(e)
session.flush()

session.clear()

if 1: #nofilter
    session = create_session()
    r = session.query( Engineer).select()
    print [ a.helper.name for a in r]        #->anna
    session.clear()

if 2:
    session = create_session()
    r = session.query( Engineer).select(
        (table_Engineer.c.helper_id == table_Employee.c.db_id )
        &
        (table_Employee.c.age >= 20)
    )
    print [ a.helper.name for a in r]    #?nothing
    session.clear()

if 3:
    session = create_session()
    from sqlalchemy.ext.selectresults import SelectResults
    q = SelectResults( session.query( Engineer))
    r = q.join_to( 'helper').select( (table_Employee.c.age >= 20) )
    print [ a.helper.name for a in r]    #?nothing
    session.clear()

if 4:
    session = create_session()
    q = session.query( Engineer)
    r = q.select( q.join_via( ['helper']) & (table_Employee.c.age >= 20))
    ##raises exception - but ONLY if none of other ways above is executed
    print [ a.helper.name for a in r]