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

Although @droggo is correct in that finders, where queries, and criteria queries are preferred over HQL, for your case those options are not available.

If the properties you needed to sort by were in the same domain class, you would be able to use a derived property. You wouldn't be able to unit test it, and it's database-dependent, but you'd be able to use any GORM querying method. HQL might be ugly in comparison, but it can do what you need without much fanfare.

Getting it done with HQL

Basically, you'll be sorting by a calculated value by using a combination of CASE WHEN and CONCAT:

def customers = Customer.executeQuery "SELECT cust FROM Customer AS cust LEFT OUTER JOIN cust.company AS comp LEFT OUTER JOIN cust.contact AS cont ORDER BY CASE WHEN comp.id IS NULL THEN CONCAT(cont.firstName, cont.lastName) ELSE comp.name END"

That should do it. The query will return a List of Customer instances sorted either by Company name or Contact first and last name.

Answer 2

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);
    }
}