This question has been flagged
2 Replies
21740 Views
Avatar
Prakash

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

Avatar
Discard
Torsten Francke

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

Prakash
Author

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

Avatar
Torsten Francke
Best Answer

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

Avatar
Discard
Prakash
Author

thanks for reply i will try

Prakash
Author

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 ?

Torsten Francke

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

Prakash
Author

Thanks solved

Prakash
Author

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

Avatar
Ethan Furman
Best Answer

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
Avatar
Discard