Odoo Help

Welcome!

This community 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.

2

order by selection field in List view

By
Prakash
on 8/6/13, 6:33 AM 6,145 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

Prakash
on 8/12/13, 7:47 AM
3
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

Prakash
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 ?

Prakash
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

Prakash
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

Prakash
on 11/29/13, 7:01 AM
2
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

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

2 follower(s)

Stats

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