How to Order by using Switch Case/Function in Hibernate/Grails

Question

I have the following hibernate/grails domain classes, this is a legacy database and i don't have an option to remodel these.

class Contact {
    String firstName
    String lastName
}

class Company {
    String name
}

class Customer {
    Company company
    Contact contact
}

Customer can be a Company or a Contact, So now i have a usecase where i need to sort the customer by names. (In case of company use name and incase of contact use firstName + lastName).

I have looked around hibernate source to find if there is a way to hack in to support order by using switch case, but not successful. Anyone ran into similar usecase ? How did you handle this usecase or any suggestions ?

Thanks.

Answer 1

Finally able to come up with a solution. This is not a very generic solution, tailored specific to my usecase.

def instances = searchCriteria.list(criteria) {

    createAlias('customer', 'cust', CriteriaSpecification.INNER_JOIN)
    createAlias('cust.company', 'cmp', CriteriaSpecification.LEFT_JOIN)
    createAlias('cust.user', 'user', CriteriaSpecification.LEFT_JOIN)

     if(sortCol) {
        if(sortCol == 'customer') {
            order(CustomerOrder.by('cust', 'cmp', direction))
        }
        else {
            order(sortCol, direction)
        }
    }
}

Here is my CustomerOrder Class that extends the Hibernate Order

import org.hibernate.criterion.Order;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;

public class CustomerOrder extends Order {

    private String companyAlias
    private String userAlias
    private boolean ascending

    protected CustomerOrder(String userAlias, String companyAlias, boolean ascending) {
        super("", true);
        this.companyAlias = companyAlias
        this.userAlias = userAlias
        this.ascending = ascending
    }

    public String toString() {
        return companyAlias  + "-" + userAlias;
    }

    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        String[] firstName = criteriaQuery.getColumnsUsingProjection(
            criteria, "${userAlias}.firstName");
        String[] lastName = criteriaQuery.getColumnsUsingProjection(
            criteria, "${userAlias}.lastName");

        String[] companyId = criteriaQuery.getColumnsUsingProjection(
            criteria, "${companyAlias}.id");
        String[] companyName = criteriaQuery.getColumnsUsingProjection(
            criteria, "${companyAlias}.name");

        """
            CASE WHEN ${companyId[0]} IS NULL
                THEN LOWER(RTRIM(LTRIM(${lastName[0]} + ', ' + ${firstName[0]})))
                ELSE LOWER(RTRIM(LTRIM(${companyName[0]})))
            END ${ascending ? "asc" : "desc" }
        """
    }

    public static Order by(String userAlias, String companyAlias, String direction) {
        boolean ascending = (!direction || direction.equalsIgnoreCase('asc'));
        return new CustomerOrder(userAlias, companyAlias, ascending);
    }
}