orm when using nested query in select with an order_by, incorrectly names sort column.

I have a mapped class.. lets call it Data with a few properties

Data.id (primary key), Data.a, Data.b, Data.c

I want to query a few of these objects out.. but they need to be sorted by some arbitrary data

arbitrary_data=select ( OtherClass.c.somedata, and_(....)).alias('somedata')

ok.. now query the data:

dat=Data.select( and_(.....), from_obj=[ datas.join(arbitrary_data,arbitrary_data.c.id==datas.c.id) ] , order_by=asc(arbitrary_data.c.somedata))

Now, the generated sql is in the form (with query.py deciding it needs to nest the query):

select datas.id as datas_id, datas.a as datas_a .... etc. from (select datas.id as datas_id, arbitrary_data.somedata as arbitrary_data_somedata from datas join (my arbitrary_data table query with where clause ) as arbitrary_data where ..... order by arbitrary_data.somedata ) as tbl_row_count join datas on ... order by arbitrary_data.somedata

The last line is the problem.. The from clause renames the column to arbitrary_data_somedata but the order by clause uses the inner form with a . still.

The error: missing FROM-clause entry for table "arbitrary_data" (because that table only exists on the inner aliased table)

Anyhow, if I rename the sort on the outer query to use the underscore manually, the query returns the correct results in the correct order.

The ORM is using the nesting feature, because my query is requesting to eagerload a property AND there is a limit clause (the order_by by itself doesn't do it)

Comments (3)