limit/offset and properties problem in 0.3.8

Query generation in select/join mapper with some relations and limit/offset. It worked in 0.3.6 !!

#!/usr/bin/env python
from tracker.model import *
from sqlalchemy import *
import sys


try:
    global_connect(sys.argv[1](1))
except Exception, e:
    print """*** Exception caught:"""
    print e

session = create_session()
default_metadata.engine.echo = True


tsk_cnt_join = outerjoin(project_t, task_t, task_t.c.prj_id==project_t.c.id)
ss = select([func.count(task_t.c.id).label('tasks_number')](project_t.c.id.label('prj_id'),), 
            from_obj=[tsk_cnt_join](tsk_cnt_join), group_by=[project_t.c.id](project_t.c.id)).alias('prj_tsk_cnt_s')
j = join(project_t, ss, project_t.c.id == ss.c.prj_id)


j  = outerjoin( task_t, message_t, task_t.c.id==message_t.c.task_id)
jj = select([ task_t.c.id.label('task_id'),
              func.count(message_t.c.id).label('props_cnt')],
              from_obj=[j](j), group_by=[task_t.c.id](task_t.c.id)).alias('prop_c_s')
jjj = join(task_t, jj, task_t.c.id == jj.c.task_id)
#        jjj = outerjoin(task_effort_t, jjj, task_effort_t.c.task_id == jjj.c.task_id)

class cls(object):pass

props =dict(type=relation(Task_Type, lazy=False))
cls.mapper = mapper( cls, jjj, order_by=[desc(task_t.c.updated)](desc(task_t.c.updated)), properties=props)

#works
cls.mapper.properties = {}
for t in session.query(cls.mapper).limit(10).offset(0).list():
    print t.id, t.title, t.props_cnt

# works
for t in select([jjj](jjj), from_obj=[jjj](jjj),
               limit=10, offset=0).execute(bind_to=session.bind_to):
    print t.id, t.title, t.props_cnt

orm.clear_mapper(cls.mapper)
cls.mapper = mapper( cls, jjj, order_by=[desc(task_t.c.updated)](desc(task_t.c.updated)), properties=props)

# not works
for t in session.query(cls.mapper).limit(10).offset(0).list():
    print t.id, t.title, t.props_cnt

The log:

2007-06-06 16:26:37,899 INFO sqlalchemy.engine.base.Engine.0x..cL SELECT task.updated AS task_updated, task.task_type_id AS task_task_type_id, task.created AS task_created, task.prj_id AS task_prj_id, task.status_id AS task_status_id, task.budget AS task_budget, task.summary AS task_summary, task.publish_end AS task_publish_end, task.deadline AS task_deadline, task.short_summary AS task_short_summary, task.title AS task_title, prop_c_s.props_cnt AS prop_c_s_props_cnt, task.id AS task_id 
FROM task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id ORDER BY task.updated DESC 
 LIMIT 10 OFFSET 0
2007-06-06 16:26:37,900 INFO sqlalchemy.engine.base.Engine.0x..cL [ewfrwfsdf 0
201 sss 0
200 Documentation 9
187 Web Design 8
188 Testing 0
189 Project Management 0
190 Icons Design 0
191 Specification Development 9
192 Project Management 0
193 Specification Development 0
2007-06-06 16:26:37,910 INFO sqlalchemy.engine.base.Engine.0x..cL SELECT task.id, task.status_id, task.created, task.updated, task.deadline, task.publish_end, task.prj_id, task.title, task.summary, task.short_summary, task.budget, task.task_type_id, prop_c_s.task_id, prop_c_s.props_cnt 
FROM task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id 
 LIMIT 10 OFFSET 0
2007-06-06 16:26:37,910 INFO sqlalchemy.engine.base.Engine.0x..cL [](]
202)
1 Software Development 0
2 Web Design 0
3 Documentation 0
4 Other 0
5 Web Design 9
6 Specification Development 4
7 Graphics Design 0
8 Software Development 1
9 Project Management 6
10 Application Deployment 8
2007-06-06 16:26:37,931 INFO sqlalchemy.engine.base.Engine.0x..cL SELECT task.updated AS task_updated, task.task_type_id AS task_task_type_id, task.created AS task_created, task.prj_id AS task_prj_id, task.status_id AS task_status_id, task.budget AS task_budget, task.summary AS task_summary, task.publish_end AS task_publish_end, task.deadline AS task_deadline, task.short_summary AS task_short_summary, task.title AS task_title, prop_c_s.props_cnt AS prop_c_s_props_cnt, anon_fe68.description AS anon_fe68_description, anon_fe68.display_order AS anon_fe68_display_order, anon_fe68.id AS anon_fe68_id, anon_fe68.name AS anon_fe68_name, task.id AS task_id 
FROM (SELECT task.id AS task_id, prop_c_s.task_id AS prop_c_s_task_id, task.updated AS task_updated 
FROM task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id ORDER BY task.updated DESC 
 LIMIT 10 OFFSET 0) AS tbl_row_count, task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id LEFT OUTER JOIN task_type AS anon_fe68 ON anon_fe68.id = task.task_type_id 
WHERE task.id = tbl_row_count.task_id AND task.id = tbl_row_count.prop_c_s_task_id AND prop_c_s.task_id = tbl_row_count.task_id AND prop_c_s.task_id = tbl_row_count.prop_c_s_task_id ORDER BY tbl_row_count.task_updated DESC, anon_fe68.id
2007-06-06 16:26:37,931 INFO sqlalchemy.engine.base.Engine.0x..cL []
Traceback (most recent call last):
  File "./qqq.py", line 53, in <module>
    for t in session.query(cls.mapper).limit(10).offset(0).list():
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 829, in list
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 842, in __iter__
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 326, in select_whereclause
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 955, in _select_statement
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 857, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 916, in instances
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 1414, in _instance
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 924, in identity_key_from_row
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 1173, in __getitem__
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 994, in _get_col
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 931, in _convert_key
sqlalchemy.exceptions.NoSuchColumnError: "Could not locate column in row for column 'Column('task_id',Integer(),primary_key=True,nullable=False)'"

Comments (1)