Odoo Help


order by selection field in List view

on 8/6/13, 6:33 AM 8,177 views

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

it is maybe helpfull to see the complete sql statment which was produce by openerp

initOS GmbH, Torsten Francke
on 8/12/13, 7:43 AM

Overridden search method and applied the sql statement same issue please let me provide more details to how to fix issues ? thanks

on 8/12/13, 7:47 AM
Torsten Francke
On 8/12/13, 5:40 PM

per default you can only sort by fields but you can override this

the following code:

    def _generate_order_by(self, order_spec, query):
        my_order = "CASE WHEN state='draft'  THEN 0   WHEN state = 'assigned'  THEN 1 ELSE 2 END,  date desc"            
        if order_spec:
            return super(sale_order, self)._generate_order_by(order_spec, query) + ", " + my_order
        return " order by " + my_order

to your sales_order model. I think this solves your problem. order_spec is given by the client or other order statment and add your stuff as well.

Hint: Overriding this method can add security holes like sql-injection if you do not check carefully your code and know what you do

UPDATE: I build a more generic version to update the sorting of 'state' as defined in the model instead of the name. I publish in our blog: http://blog.initos.com/2013/08/15/openerp-sortieren-nach-prozess-status/ with a german problem description but you can see the code

thanks for reply i will try

on 8/13/13, 5:49 PM

In the above code works well in the number of records. but in the empty record shows below issue File "C:\Program Files (x86)\OpenERP 7.0-20130308-002138\Server\server.\openerp\sql_db.py", line 226, in execute ProgrammingError: syntax error at or near "by" LINE 1: ...".id FROM "object.name" WHERE FALSEorder by CASE WH... please guide me how to fix ?

on 9/10/13, 1:02 PM

add a space in 'return " order by " + my_order' before 'order by', i updated the code above

initOS GmbH, Torsten Francke
on 9/11/13, 5:27 AM

Thanks solved

on 9/11/13, 6:27 AM

Hi Markus, The above code worked for default view i also want the same functionality In the List view Header State Field clicked by order by ASC and order by DESC (based on the above _generate_order_by query). I think need to search method override is any sample code available based on this. Thanks

on 11/29/13, 7:01 AM
Ethan Furman
On 12/2/13, 7:02 PM

A version of Markus' answer from his blog. This one only changes the state ordering if state was in the query.

def _generate_order_by(self, order_spec, query):
    "correctly orders state field if state is in query"
    order_by = super(this_table_class, 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

About This Community

This platform is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.


Odoo Training Center

Access to our E-learning platform and experience all Odoo Apps through learning videos, exercises and Quizz.

Test it now

Question tools

2 follower(s)


Asked: 8/6/13, 6:33 AM
Seen: 8177 times
Last updated: 3/16/15, 8:10 AM