Optional order in OpenERP 7.0 list view

Question

In the OpenERP 7 list view, I want to show the sorting of the status value in the draft order, assign and cancel now in Asc or Desc. But in my case, we need sorting in the draft order, assignment and cancellation of the state. Based on applied in order in python file

For example, in SQL code -

select state, date from object_name ORDER BY CASE WHEN state = 'draft' THEN 0 WHEN state = 'assigned' THEN 1 WHEN state = 'cancel' THEN 2 ELSE 3 END, date desc 

The above sql code used in python

 _order = ("CASE WHEN state='draft' THEN 0", "WHEN state = 'assigned' THEN 1", "ELSE 2 END, date desc") 

In the above selection of the sort selection value working in pg_admin, but in python code it shows an error below

 Invalid "order" specified. A valid "order" specification is a comma-separated list of valid field names (optionally followed by asc/desc for the direction) 

Based on this sort order by selection value, how to apply in OpenERP? The re-search method also applied the same SQL query, but shows the same problem.

Answer 1

Try creating a function field with the store attribute that loads the function when the state changes. eg

 def _get_state(cr, uid, ids,field_name, context=None): res={} for obj in self.browse(cr, uid, ids, context): res[obj.id] = (obj.state=='draft' and 0) or (obj.state=='assigned' and 1) or (obj.state=='cancel' and 2) or 3 return res _columns = { current_state_num: fields.function(_get_state,string='Current state',type='integer',store={'your.current.model.name':(lambda cr, uid, ids, context:ids,['state'],20)}) } _order = "current_state_num,date desc" 

Answer 2

A possible solution is to execute the request itself from a python function. Then you can call this function from an action or another method. eg.

 def _my_custom_search(self, cr, uid, object_name, context=None): sql_req = """ select id, state, date from %s ORDER BY CASE WHEN state = 'draft' THEN 0 WHEN state = 'assigned' THEN 1 WHEN state = 'cancel' THEN 2 ELSE 3 END, date desc""" %(object_name, ) cr.execute(sql_req) res = cr.fetchall() return res 

You need to select the id field in addition to the fields you order. Another option is to change the default filter to view. Adding a default filter as a tree

Answer 3

Another workaround would be sorting by status and changing the order of your states in the field:

 'state': fields.selection([('draft','Draft'),('assigned','Assigned'), ('cancel','Cancelled'),('other' .... ... _order = "state, date desc" 

OR you can create a new function field (for example, sort_priority) and use a similar function to assign a value to priority, and then arrange this field.

Answer 4

If you can read German, this blog post has a great solution. Here is an adapted version:

 class some_table(osv.Model): _name = 'some.table' _columns = { ... ... 'state' : field.selection(...), ... ... def _generate_order_by(self, order_spec, query): "correctly orders state field if state is in query" order_by = super(some_table, self)._generate_order_by(order_spec, query) if order_spec and 'state ' in order_spec: state_column = self._columns['state'] state_order = 'CASE ' for i, state in enumerate(state_column.selection): state_order += "WHEN %s.state='%s' THEN %i " % (self._table, state[0], i) state_order += 'END ' order_by = order_by.replace('"%s"."state" ' % self._table, state_order) return order_by