Order by selection in List view of OpenERP 7.0

Question

In OpenERP 7 list view I want to shows the state value sort in the order draft, assigned and cancel currently shows in Asc or Desc. But in my case we need sorting in the order draft, assigned and cancel state. Based on applied in order by in python file

For example in the SQL the 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 applied in the python

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

In the above query sorting selection value working in the pg_admin but in the python code its shows below error

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 sorting order by selection value how to apply in OpenERP? Override search method also applied the same sql query but shows same issue.

Answer 1

Try creating a functional field with store attribute,which loads the function when state changes. for example

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 executing the query itself from a python function. Then you can either call that function from an action or another method. e.g.

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'll be ordering by. Another possibility is changing default filter on a view. Adding default filter on tree view

Answer 3

Another workaround would be to sort by status and change 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 (eg sort_priority) and use a similar function to assign value to the priority and then order by that field.

Answer 4

If you can read German, this blog post has an excellent solution. Here's an adapted version of it:

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