Why is "ORDER BY field_name LIKE 'start%'" not working in Hibernate/Spring/JPA while it does work in plain SQL?

Question

Problem:

I have a query that needs a LIKE in the ORDER BY statement. While that works in plain SQL I cant get it to work in JPQL or CriteriaQuery; So the problem is always with the order by LOWER(i.name) like "abc%" desc!

This query works and lists the items where the name contains the search term. It returns first the items where the name starts with the search term and then all others. Here is the query in plain SQL:

SELECT i.name, i.popularity
FROM item i
    left join picture p on p.id=i.picture_id and p.disabled=false
    left join picture b on p.id=i.background_picture_id and b.disabled=false
WHERE i.disabled=false and LOWER(i.name) like "%abc%"
order by LOWER(i.name) like "abc%" desc, i.popularity desc
limit 50;

While this is a start, I would prefer to use JpaRepository with @Query(".."), JPQL or CriteriaQuery to stay database independent.

@Query("SELECT i FROM ItemEntity i " +
    "left join i.picture p on p.disabled=false " +
    "left join i.backgroundPicture b on b.disabled=false " +
    "WHERE i.disabled=false and LOWER(i.name) like %:partialName% " +
    "order by (LOWER(i.name) like :partialNameOrderBy%) desc, i.popularity desc")
Page<ItemListView> findActiveItemsWhereNameContains(@Param("partialName") String partialNameLowerCase, @Param("partialNameOrderBy") String partialNameLowerCaseOrderByStartsWith, Pageable pageable);

This JPQL query results in:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: like near line 1, column

So I tried this as a CriteriaQuery:

@Override
public Page<ItemListView> findActiveItemsWhereNameContains(String partialName, int maxResults) {
    String partialNameLowerCase = StringUtils.lowerCase(StringUtils.trimToEmpty(partialName));
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ItemListView> cq = cb.createQuery(ItemListView.class);
    Root<ItemEntity> item = cq.from(ItemEntity.class);
    Join<ItemEntity, Ct2PictureEntity> picture = item.join(ItemEntity_.picture, JoinType.LEFT);
    picture.on(cb.and(picture.getOn()), cb.isFalse(picture.get(Ct2PictureEntity_.disabled)));
    Join<ItemEntity, Ct2PictureEntity> backgroundPicture = item.join(ItemEntity_.backgroundPicture, JoinType.LEFT);
    picture.on(cb.and(backgroundPicture.getOn()), cb.isFalse(backgroundPicture.get(Ct2PictureEntity_.disabled)));

    Predicate itemIsActive = cb.isFalse(item.get(ItemEntity_.disabled));
    Predicate itemNameContainsSearch = cb.like(item.get(ItemEntity_.name), "%" + partialNameLowerCase + "%");
    Predicate itemNameStartsWithSearch = cb.like(item.get(ItemEntity_.name), partialNameLowerCase + "%");

    cq.select(cb.construct(
        ItemListViewDto.class,
        item.get(ItemEntity_.id),
        item.get(ItemEntity_.name),
        item.get(ItemEntity_.popularity),
        CriteriaBuilderUtils.getMediumAndThumbnailPicture(cb, picture),
        CriteriaBuilderUtils.getMediumPicture(cb, backgroundPicture)))
    .where(cb.and(itemIsActive, itemNameContainsSearch)).orderBy(cb.desc(itemNameStartsWithSearch), cb.desc(item.get(ItemEntity_.popularity)));

    TypedQuery<ItemListView> query = entityManager.createQuery(cq);

    Pageable pageable = PageRequest.of(0, maxResults);
    int totalRows = query.getResultList().size();
    query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
    query.setMaxResults(pageable.getPageSize());

    return new PageImpl<>(query.getResultList(), pageable, totalRows);
}

This query results in:

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: like near line 1, column

---

Question:

How can I write this query using JPA or Hibernate and avoiding native SQL?

Answer 1

The problem here is that Hibernate does not coerce predicates to boolean expressions. You will have to use e.g. CASE WHEN (LOWER(i.name) like :partialNameOrderBy%) THEN 1 ELSE 0 END instead which you can put into the order by clause.