3.5.3.4.5. Custom Sorting
Since7.1

Sorting of UI tables by entity attributes is performed by CollectionContainerSorter which is set for a CollectionContainer. The standard implementation sorts data in memory if it fits in one page of loaded data, otherwise it sends a new request to the database with the appropriate "order by" clause. The "order by" clause is created by the JpqlSortExpressionProvider bean on the middle tier.

Some entity attributes may require a special implementation of sorting. Below we explain how to customize sorting on a simple example: suppose there is a Foo entity with a number attribute of type String, but we know that the attribute actually stores only numeric values. So we want the sort order to be 1, 2, 3, 10, 11. With the default behavior, the order would be 1, 10, 11, 2, 3.

First, create a subclass of the CollectionContainerSorter class in the web module for sorting in memory:

package com.company.demo.web;

import com.company.demo.entity.Foo;
import com.haulmont.chile.core.model.MetaClass;
import com.haulmont.chile.core.model.MetaPropertyPath;
import com.haulmont.cuba.core.entity.Entity;
import com.haulmont.cuba.core.global.Sort;
import com.haulmont.cuba.gui.model.BaseCollectionLoader;
import com.haulmont.cuba.gui.model.CollectionContainer;
import com.haulmont.cuba.gui.model.impl.CollectionContainerSorter;
import com.haulmont.cuba.gui.model.impl.EntityValuesComparator;

import javax.annotation.Nullable;
import java.util.Comparator;
import java.util.Objects;

public class CustomCollectionContainerSorter extends CollectionContainerSorter {

    public CustomCollectionContainerSorter(CollectionContainer container,
                                           @Nullable BaseCollectionLoader loader) {
        super(container, loader);
    }

    @Override
    protected Comparator<? extends Entity> createComparator(Sort sort, MetaClass metaClass) {
        MetaPropertyPath metaPropertyPath = Objects.requireNonNull(
                metaClass.getPropertyPath(sort.getOrders().get(0).getProperty()));

        if (metaPropertyPath.getMetaClass().getJavaClass().equals(Foo.class)
                && "number".equals(metaPropertyPath.toPathString())) {
            boolean isAsc = sort.getOrders().get(0).getDirection() == Sort.Direction.ASC;
            return Comparator.comparing(
                    (Foo e) -> e.getNumber() == null ? null : Integer.valueOf(e.getNumber()),
                    EntityValuesComparator.asc(isAsc));
        }
        return super.createComparator(sort, metaClass);
    }
}

If you need the customized sorting just in a few screens, you can instantiate CustomCollectionContainerSorter right in the screen:

public class FooBrowse extends StandardLookup<Foo> {

    @Inject
    private CollectionContainer<Foo> fooDc;
    @Inject
    private CollectionLoader<Foo> fooDl;

    @Subscribe
    private void onInit(InitEvent event) {
        CustomCollectionContainerSorter sorter = new CustomCollectionContainerSorter(fooDc, fooDl);
        fooDc.setSorter(sorter);
    }
}

If your sorter defines some global behavior, create your own factory which instantiates sorters system-wide:

package com.company.demo.web;

import com.haulmont.cuba.gui.model.*;
import javax.annotation.Nullable;

public class CustomSorterFactory extends SorterFactory {

    @Override
    public Sorter createCollectionContainerSorter(CollectionContainer container,
                                                  @Nullable BaseCollectionLoader loader) {
        return new CustomCollectionContainerSorter(container, loader);
    }
}

Register the factory in web-spring.xml to override the default factory:

<bean id="cuba_SorterFactory" class="com.company.demo.web.CustomSorterFactory"/>

Now create own implementation of JpqlSortExpressionProvider in the core module for sorting at the database level:

package com.company.demo.core;

import com.company.demo.entity.Foo;
import com.haulmont.chile.core.model.MetaPropertyPath;
import com.haulmont.cuba.core.app.DefaultJpqlSortExpressionProvider;

public class CustomSortExpressionProvider extends DefaultJpqlSortExpressionProvider {

    @Override
    public String getDatatypeSortExpression(MetaPropertyPath metaPropertyPath, boolean sortDirectionAsc) {
        if (metaPropertyPath.getMetaClass().getJavaClass().equals(Foo.class)
                && "number".equals(metaPropertyPath.toPathString())) {
            return String.format("CAST({E}.%s BIGINT)", metaPropertyPath.toString());
        }
        return String.format("{E}.%s", metaPropertyPath.toString());
    }
}

Register the expression provider in spring.xml to override the default one:

<bean id="cuba_JpqlSortExpressionProvider" class="com.company.demo.core.CustomSortExpressionProvider"/>