[sqlalchemy] Re: sorting mapped class by joined subquery result (error)
quick easy fix is to use result set mapping instead, or feed a full
select() statement into query.select(), which will skip the whole
compilation step.
i would like a small test case attached for this one so i can play
with it, though. for example i dont see why its deciding to use the
nesting feature of the compilation in the first place.
On Jan 31, 1:12 pm, Dennis [EMAIL PROTECTED] wrote:
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 ( [Data.c.id, 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.
I believe the faulty behavior starts at line 455 in orm/query.py
(trunk).I'm not sure if it is the Aliasizer that is not converting
the column. Anyhow, I need this to work so I don't have to write my
great big huge dynamic query out by hand so I'll be digging into the
sqlalchemy code for a second.
Is there is quick easy fix though?
Thanks
-Dennis
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sorting mapped class by joined subquery result (error)
Sorry if this posts twice... I didn't get a Message has been sent page last time.. I posted a bug with a test case here: http://www.sqlalchemy.org/trac/ticket/449 Thanks Dennis --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sorting mapped class by joined subquery result (error)
I've created a bug with an attached test: http://www.sqlalchemy.org/trac/ticket/449 Thanks! -Dennis --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sorting mapped class by joined subquery result (error)
Just a quick note, I tried out your suggestion to pass in a select
statement.
That does indeed work. There is an issue though, I tried using the
contains_eager('myproperty') as noted in the docs and that only worked
in combination with eagerload('myproperty'). I think the reason is
that in the docs, the mapper is defined with lazy=False. That was a
little confusing, but perhaps it is supposed to be that way. I would
have thought that contains_eager implies eagerload.
-Dennis
On Jan 31, 12:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
quick easy fix is to use result set mapping instead, or feed a full
select() statement into query.select(), which will skip the whole
compilation step.
i would like a small test case attached for this one so i can play
with it, though. for example i dont see why its deciding to use the
nesting feature of the compilation in the first place.
On Jan 31, 1:12 pm, Dennis [EMAIL PROTECTED] wrote:
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 ( [Data.c.id, 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.
I believe the faulty behavior starts at line 455 in orm/query.py
(trunk).I'm not sure if it is the Aliasizer that is not converting
the column. Anyhow, I need this to work so I don't have to write my
great big huge dynamic query out by hand so I'll be digging into the
sqlalchemy code for a second.
Is there is quick easy fix though?
Thanks
-Dennis
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sorting mapped class by joined subquery result (error)
On Jan 31, 4:57 pm, Dennis [EMAIL PROTECTED] wrote: little confusing, but perhaps it is supposed to be that way. I would have thought that contains_eager implies eagerload. great idea. i threw that into rev 2284. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
