from sqlalchemy import *
db = create_engine( 'sqlite:///:memory:')
meta = BoundMetaData( db)
import sys
meta.engine.echo= 'echo' in sys.argv

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, ),
    Column( 'helper_id', Integer, ForeignKey( 'Employee.db_id', ), ),
)

meta.create_all()

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

mapper_Employee = mapper( Employee, table_Employee,)
mapper_Employee.add_property( 'helper', relation( Employee,
            foreignkey= table_Employee.c.helper_id,
            lazy= True,
            primaryjoin= table_Employee.c.helper_id == table_Employee.c.db_id,
            remote_side= table_Employee.c.db_id,
            uselist= False,
            post_update=True,
            ) )

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

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

e = Employee()
e.name = 'ben'
e.age = 30

a.helper = e

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

session.clear()

if 1: #no filter
    print 'all ------------------'
    session = create_session()
    r = session.query( Employee).select()
    print [ a.name for a in r]        #2 items

if 2:
    print '2 ------------------'
    session = create_session()
    te = table_Employee.alias( 'bbb')
    r = session.query( Employee).select(
        (table_Employee.c.helper_id == te.c.db_id )
        &
        (te.c.age >= 20)
    )
    print [ a.name for a in r]       #1 item - this now is ok

if 3:
    print '3 ------------------'
    try:
        session = create_session()
        from sqlalchemy.ext.selectresults import SelectResults
        q = SelectResults( session.query( Employee))
        r = q.join_to( 'helper').select( table_Employee.alias('zzz').c.age >= 20)
        print [ a.name for a in r]    #?exception
    except Exception,e:
        import traceback; traceback.print_exc()

if 4:
    print '4 ------------------'
    session = create_session()
    q = session.query( Employee)
    r = q.select( q.join_via( ['helper']) & (table_Employee.alias('xxx').c.age >= 20))
    print [ a.name for a in r]   #nothing