Sorting OpenERP table by functional field

Question

On search screens, users can sort the results by clicking on a column header. Unfortunately, this doesn't work for all columns. It works fine for regular fields like name and price that are stored on the table itself. It also works for many-to-one fields by joining to the referenced table and using the default sort order for that table.

What doesn't work is most functional fields and related fields. (Related fields are a type of functional field.) When you click on the column, it just ignores you. If you change the field definition to be stored in the database, then you can sort by it, but is that necessary? Is there any way to sort by a functional field without storing its values in the database?

Don Kirkby Don Kirkby · Accepted Answer · 2013-03-25T17:24:34

Apparently there has been some discussion of this, and CampToCamp posted a merge proposal with a general solution. There's also some discussion in their blog.

I haven't tried their solution yet, but I did create a specific solution for one field by overriding the _generate_order_by() method. Whenever the user clicks on a column header, _generate_order_by() tries to generate an appropriate ORDER BY clause. I found that you can actually put a SQL subquery in the ORDER BY clause to reproduce the values for a functional field.

As an example, we added a functional field to display the first supplier's name for each product.

def _product_supplier_name(self, cr, uid, ids, name, arg, context=None):
    res = {}
    for product in self.browse(cr, uid, ids, context):
        supplier_name = ""
        if len(product.seller_ids) > 0:
            supplier_name = product.seller_ids[0].name.name
        res[product.id] = supplier_name
    return res

In order to sort by that column, we overrode _generate_order_by() with some pretty funky SQL. For any other column, we delegate to the regular code.

def _generate_order_by(self, order_spec, query):
    """ Calculate the order by clause to use in SQL based on a set of
    model fields. """
    if order_spec != 'default_partner_name':
        return super(product_product, self)._generate_order_by(order_spec,
                                                               query)
    return """
        ORDER BY
        (
        select  min(supp.name)
        from    product_supplierinfo supinf
        join    res_partner supp
        on      supinf.name = supp.id
        where   supinf.product_id = product_product.id
        ),
        product_product.default_code
        """

Sorting OpenERP table by functional field

2 Answers