python - Invalid reference to table while order_by applied -

i have sqlalchemy data model (other fields missed):

class region(base):     __tablename__ = 'region'     id = db.column(db.integer, primary_key=true)  class district(base):     __tablename__ = 'district'     id = db.column(db.integer, primary_key=true)     region_id = db.column(db.integer, foreignkey(region.id))     region = relationship(region) 

and build query:

session.query(district)\     .options(joinedload(district.region))\     .order_by(region.name, region.id)\     .slice(0, 25) 

the query emits error programmingerror: (programmingerror) invalid reference from-clause entry table "region". after investigation raw sql reason of error found. order by clause non correct:

select district.id district_id,      district.name district_name,      district.region_id district_region_id,      region_1.id region_1_id district  left outer join region region_1 on region_1.id = district.region_id  order region.name, region.id limit 25 

into order by clause uses region instead of correct alias region_1 , raises error.

how build correct query in case?

take sqlalchemy documentation the zen of eager loading

they describe problem, , solution, be:

in [10]:     ​ q = session.query(district)\     .join(district.region)\     .options(joinedload(district.region))\     .order_by(region.name, region.id)\     .slice(0, 25) ​ print(q) ​ select district.id district_id, district.region_id district_region_id, district.name district_name, region_1.id region_1_id, region_1.name region_1_name  district join region on region.id = district.region_id left outer join region region_1 on region_1.id = district.region_id order region.name, region.id  limit ? offset ? 

(i had add names classes posted way).

relevant quote:

what see above our usage of query.join() supply join clauses we’d use in subsequent query criterion, whereas our usage of joinedload() concerns loading of user.addresses collection, each user in result. in case, 2 joins appear redundant - are.

(replace user.addresses district.region in case).

and if follow link can make again in 1 join this:

in [15]:  sqlalchemy.orm import aliased, outerjoin, contains_eager ​ ralias = aliased(region) ​ q2 = session.query(district).\     outerjoin(ralias, district.region).\     options(contains_eager(district.region, alias=ralias)). \     order_by(region.name, region.id).\     slice(0, 25) ​ print(q2) ​ select region_1.id region_1_id, region_1.name region_1_name, district.id district_id, district.region_id district_region_id, district.name district_name  district left outer join region region_1 on region_1.id = district.region_id order region.name, region.id  limit ? offset ? 

hope helps.