CODEWITHSUNDEEP
pythonpostgresqlormodooodoo-14
Satya Dev Yadav
Satya Dev Yadav

Reputation: 175

How to get record in true alphabetically ( case insensitive ) sort order in odoo?

My use-case is to list the records in a tree or kanban view by name but it should be case insensitive. Current state Eg. I have four records like

Apple
Orange
apple
Banana

In default the ordering is:

Apple
Banana
Orange
apple

And the expected ordering is:

Apple
apple
Banana
Orange

How to achieve this? Is there any ORM method to do this?

Upvotes: 0

Views: 1396

Answers (2)

Kenly
Kenly

Reputation: 26718

When Odoo searches for records, it will attempt to construct an appropriate ORDER BY clause based on order_spec, which must be a comma-separated list of valid field names, optionally followed by an ASC or DESC direction.

The order by clause is computed based on _order attribute and can be overridden by the order parameter in the search function.

You can override the _generate_order_by_inner function to make it possible to pass function names used in SQL queries directly in _order attribute (field_name:function) for char fields.

Example:

class Fruits(models.Model):
    _name = 'fruit.fruit'
    _order = 'name:lower'

    name = fields.Char()

    @api.model
    def _generate_order_by_inner(self, alias, order_spec, query, reverse_direction=False, seen=None):
        if seen is None:
            seen = set()
        self._check_qorder(order_spec)

        order_by_elements = []
        for order_part in order_spec.split(','):
            order_split = order_part.strip().split(' ')
            order_field = order_split[0].strip()
            order_direction = order_split[1].strip().upper() if len(order_split) == 2 else ''
            if reverse_direction:
                order_direction = 'ASC' if order_direction == 'DESC' else 'DESC'
            do_reverse = order_direction == 'DESC'
            # ------------------------------------------------------------------
            func_split = order_field.strip().split(':')
            order_field = func_split[0].strip()
            func = func_split[1].strip().upper() if len(func_split) == 2 else ''
            # ------------------------------------------------------------------
            field = self._fields.get(order_field)
            if not field:
                raise ValueError("Invalid field %r on model %r" % (order_field, self._name))
            
            if order_field == 'id':
                order_by_elements.append('"%s"."%s" %s' % (alias, order_field, order_direction))
            else:
                if field.inherited:
                    field = field.base_field
                if field.store and field.type == 'many2one':
                    key = (field.model_name, field.comodel_name, order_field)
                    if key not in seen:
                        seen.add(key)
                        order_by_elements += self._generate_m2o_order_by(alias, order_field, query, do_reverse, seen)
                elif field.store and field.column_type:
                    qualifield_name = self._inherits_join_calc(alias, order_field, query)
                    if field.type == 'boolean':
                        qualifield_name = "COALESCE(%s, false)" % qualifield_name
                    # ------------------------------------------------------
                    if func and field.type == 'char':
                        qualifield_name = "%s(%s)" % (func, qualifield_name)
                    # ------------------------------------------------------
                    order_by_elements.append("%s %s" % (qualifield_name, order_direction))
                else:
                    _logger.warning("Model %r cannot be sorted on field %r (not a column)", self._name, order_field)
                    continue  # ignore non-readable or "non-joinable" fields

        return order_by_elements

You can also patch the models.BaseModel._generate_order_by_inner to use the same logic for any char field.

Example:

@api.model
def _generate_order_by_inner(self, alias, order_spec, query, reverse_direction=False, seen=None):
    ...

models.BaseModel._generate_order_by_inner = _generate_order_by_inner

To override the sort order in any view, you have just to include:

if field.type == 'char':
    qualifield_name = "lower(%s)" % (qualifield_name,)

Without redefining the order.

Upvotes: 2

CZoellner
CZoellner

Reputation: 14778

You have to think atleast about two things:

  1. Using search (or search_count) to get records from the database. You can use the parameter order to tell Odoo which order the records should be get directly sorted by the database. So for example use self.env['fruits'].search([], order="LOWER(name)") to get all fruits in "true" alphabetically order on column "name".

  2. The second part to think about is sorting records in python. But the same principal can be used there. Just lower- or uppercase while sorting.

A third part could be direct queries, but using them in this context would be the same like doing the "search" from point 1. Because the order parameter will be set in the query Odoo is building in the end of search anyways.

Upvotes: 0

Related Questions