How to sort PageRequest on String as numeric value

Question

I currently have a system in place which can filter and sort records in the database and return them as a Paged object. One of the lines is like this:

final PageRequest request = new PageRequest(this.pagingSettings.getPageNumber(),
            this.pagingSettings.getPageSize(), sortDirection, sortedBy);

This works correctly, but now I'm having the following situation. I'm trying to sort on a house number, which is a varchar in my Postgres database. For example, we have 1, 12, 111, 1004 but also 1A or 36-BASEMENT. When sorting on these (character) values, these would sort on: 1, 1004, 111, 12, 1A, ...

So, sortedBy is now a String, which in this case is houseNumber. I found out that using the ORDER BY argument ... ORDER BY NULLIF(regexp_replace(container_number, E'\\D', '', 'g'), '')::int"; in Postgres, the sorting would be exactly like I wanted: 1, 1A, 12, 111, ...

However, just changing the sortedBy String to sortedBy = "NULLIF(regexp_replace(container_number, E'\\D', '', 'g'), '')::int"; does not seem to work.

Does anyone have a suggestion on how to sort the character values in a PageRequest numerical, without changing the database?

Answer 1

So basically you need to do two things:

1. Implement custom comparator,
2. Annotate the entity class.

Ad.1.

public class HouseComparator implements Comparator<House> {
    @Override
    public int compare(House h1, House h2) {

        String s1 = h1.getHouseNumber().split("[^0-9]")[0];
        String s2 = h2.getHouseNumber().split("[^0-9]")[0];

        return s1.compareTo(s2);
    }
}

You need to add some better handling of your cases. The above comparator says, that h1 is less than h2 when it begins with a smaller number and vise versa. For this comparator 12A is equal 12B but it's up to you.

Ad.2.

@SortComparator(HouseComparator.class)
List<House> findByHouseNumber(Pageable pageable);

Answer 2

I think you could try the Spring Data JpaSort class which allows function calls.

As stated in the documentation you will have something like :

@Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)"));

You could also use it with a Pageable object.