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