Hi
I'm using and really liking SQLFORM.smartgrid. However, I'm having
issues with the sorting, particularly on the grids for the related tables.
Here is my scenario.
I have a membership database and display a grid of members.
grid = SQLFORM.smartgrid(db.member, columns=columns, csv=False,
details=False)
I want to set my orderby for the members to go by lastName, firstName.
To do so, I try this:
orderby = ['member.lastName, member.firstName']
grid = SQLFORM.smartgrid(db.member, columns=columns, csv=False,
details=False, orderby=orderby)
This works fine for sorting the member list, but when I click on one of
the related table buttons (memberCampaign in this case) in the last
column, I get the following:
Traceback (most recent call last):
File "C:\dev\web2py\gluon\restricted.py", line 194, in restricted
exec ccode in environment
File "C:/dev/web2py/applications/test/controllers/default.py", line
121, in <module>
File "C:\dev\web2py\gluon\globals.py", line 149, in <lambda>
self._caller = lambda f: f()
File "C:\dev\web2py\gluon\tools.py", line 2454, in f
return action(*a, **b)
File "C:/dev/web2py/applications/test/controllers/default.py", line
105, in members
memberCampaign='Campaigns'))
File "C:\dev\web2py\gluon\sqlhtml.py", line 1767, in smartgrid
user_signature=user_signature,**kwargs)
File "C:\dev\web2py\gluon\sqlhtml.py", line 1588, in grid
rows = dbset.select(left=left,orderby=orderby,limitby=limitby,*fields)
File "C:\dev\web2py\gluon\dal.py", line 5697, in select
return self.db._adapter.select(self.query,fields,attributes)
File "C:\dev\web2py\gluon\dal.py", line 1225, in select
sql = self._select(query,fields,attributes)
File "C:\dev\web2py\gluon\dal.py", line 1201, in _select
orderby = xorify(orderby)
File "C:\dev\web2py\gluon\dal.py", line 5637, in xorify
orderby2 = orderby2 | item
TypeError: unsupported operand type(s) for |: 'str' and 'str'
If I click on a different related table (memberTag) in the last column,
I get this:
Traceback (most recent call last):
File "C:\dev\web2py\gluon\restricted.py", line 194, in restricted
exec ccode in environment
File "C:/dev/web2py/applications/test/controllers/default.py", line
121, in <module>
File "C:\dev\web2py\gluon\globals.py", line 149, in <lambda>
self._caller = lambda f: f()
File "C:\dev\web2py\gluon\tools.py", line 2454, in f
return action(*a, **b)
File "C:/dev/web2py/applications/test/controllers/default.py", line
105, in members
memberCampaign='Campaigns'))
File "C:\dev\web2py\gluon\sqlhtml.py", line 1767, in smartgrid
user_signature=user_signature,**kwargs)
File "C:\dev\web2py\gluon\sqlhtml.py", line 1588, in grid
rows = dbset.select(left=left,orderby=orderby,limitby=limitby,*fields)
File "C:\dev\web2py\gluon\dal.py", line 5697, in select
return self.db._adapter.select(self.query,fields,attributes)
File "C:\dev\web2py\gluon\dal.py", line 1233, in select
rows = response(sql)
File "C:\dev\web2py\gluon\dal.py", line 1223, in response
self.execute(sql)
File "C:\dev\web2py\gluon\dal.py", line 1309, in execute
return self.log_execute(*a, **b)
File "C:\dev\web2py\gluon\dal.py", line 1304, in log_execute
ret = self.cursor.execute(*a,**b)
File "C:\dev\web2py\gluon\contrib\pymysql\cursors.py", line 108, in
execute
self.errorhandler(self, exc, value)
File "C:\dev\web2py\gluon\contrib\pymysql\connections.py", line 184,
in defaulterrorhandler
raise errorclass, errorvalue
InternalError: (1054, u"Unknown column 'member.lastName' in 'order clause'")
If I take the orderby out of the SQLFORM.smartgrid call, then it all
works fine.
My question is whether or not there is a way to control the orderby
clause for the related tables.
-Jim
