ORDER BY CASE in sort pageable java spring

In the same way as this question: Similar solution I would like to know if it is possible to pass ORDER BY CASE WHEN exID = 2 THEN 1 ELSE 0 END from angular to java spring or the variable to order by the exID.

Initially in my case I have params = params.append(“sort”, matSort.active + “,” + matSort.direction) where matSort.active = exID,field1,field2,field3 and matSort.direction = asc. How do I get the backend to order by variable that it isn't present in the entity DTO that I return? And when the method I use from repository usa a nativequery?

I tried to use JpaSort.unsafe but it get me a syntax error because somehow the query uses a reference table

TableEx1 te
ORDER BY
te.CASE WHEN exID.id = 2 THEN 0 ELSE 1
END,
field1 asc,
field2 asc,
field3 asc


And this is how it is until now:

angular service function

listSomething(page: MatPaginator, matSort: MatSort) {
let params: HttParams = FormsUtil.generateParamsForPaginationWithSorting(page, matSort);
return this.http.get<Page<ClassExample>>(${this.endpoint}/list-something, {params});
}


util.ts

static generateParamsForPaginationWithSorting(page: MatPaginator, matSort: MatSort): HttpParams {
let params: HttpParams = this.generateParamsForPage(page);

if (matSort != null && matSort.active != null) {
const attribute = ['exID.id', 'exID.IDType', 'EXID.IDType'].find(field => matSort.active.includes(field));

if (attribute) {
params = params.append('orderByExID', attribute);
}

params = params.append('sort', matSort.active + ',' + matSort.direction);
}
return params;
}


java spring function

// CONTROLLER:
@GetMapping(“/list-something”) public Page<ClassExample> listOrder(Pageable page, String orderByExID) {
return service.listOrder(page, orderByExID);
}

// SERVICE:
public Page<ClassExample> searchListOfSomething(Pageable page, String orderByExID) {
page = createSortForExIDOrder(orderByExID, page);
return repository.searchListOfSomething(page);
}

private Pageable createSortForExIDOrder(String orderByExID, Pageable page) {
if (orderByExID != null) {
Sort sort = JpaSort.unsafe(Sort.Direction.ASC, "CASE WHEN " + orderByExID + " = 1 THEN 0 ELSE 1 END");
sort = sort.and(paginacao.getSort());
return PageRequest.of(page.getPageNumber(), page.getPageSize(), sort);
}
return page;
}

Continue reading...