Spring Data REST: sort parameter is ignored

Question

Spring Boot 2.0.1.RELEASE project with Spring Data JPA and Spring Data REST. It seems that the sort parameter in the REST endpoint is ignored (but the same repository method do work in a unit test). The repository is the following:

@RepositoryRestResource(collectionResourceRel = "orders", path = "orders") public interface OrderRepository extends PagingAndSortingRepository<OrderEntity, Integer>, OrderRepositoryExtended { @Query(value = "FROM OrderEntity a WHERE " + " (a.orderDateTime BETWEEN :dateFrom AND :dateTo) AND" + " (" + " :searchTerm IS NULL OR" + " (LOWER(a.customer.companyName) LIKE '%' || LOWER(:searchTerm) || '%') OR" + " (LOWER(a.orderCode) LIKE '%' || LOWER(:searchTerm) || '%')" + " )" ) Page<OrderEntity> findByOrderDateTimeBetweenAndSearchTerm( @RequestParam(name = "dateFrom") @Param("dateFrom") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime dateFrom, @RequestParam(name = "dateTo") @Param("dateTo") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime dateTo, @RequestParam(name = "searchTerm") @Param("searchTerm") String searchTerm, Pageable pageable);

}

The entity (edited) is as follows:

@Entity @Table(name = "T_ORDERS") @Data @NoArgsConstructor @Cacheable(false) public class OrderEntity { @Id @GeneratedValue(strategy= GenerationType.AUTO, generator = "native") @GenericGenerator(name = "native", strategy = "native") @Column(name = "ID_ORDER") @JsonProperty("id_order") private int id; @Column(name = "DATE_ORDER") @JsonProperty("date_order") private LocalDateTime orderDateTime; }

When I try to invoke it using the exported REST endpoint, for example:

http://localhost:8080/api/v1/orders/search/findByOrderDateTimeBetweenAndSearchTerm?dateFrom=2018-01-01T00:00:00&dateTo=2018-12-01T00:00:00&sort=orderDateTime&searchTerm=O-2018

The sort parameter is ignored as you can see in the generated query (edited for clarity):

select orderentit0_.id_order as id_order1_21_ ... from t_orders orderentit0_ cross join t_customers customeren1_ where orderentit0_.id_customer=customeren1_.id_customer and (orderentit0_.date_order between ? and ?) and (? is null or lower(customeren1_.company_name) like concat('%' lower(?) '%') or lower(orderentit0_.order_code) like concat('%' lower(?) '%')) limit ?

I've already tried simplifing the WHERE expression removing the LIKE conditions but with no luck.

Thanks!

Have you tried to put sort parameter at the end of the url? And did you try another field for the sort? I made a simple demo example and it works for me — Simon Martinelli
– Simon Martinelli, Commented Feb 8, 2019 at 14:57
I tried putting sort in front and at the end of the URL; other repositories in the project works fine with similar situation. I never got working sort by a LocalDateTime field tho. — mxb
– mxb, Commented Feb 8, 2019 at 15:13
Works like a charm for me with LocalDateTime. Try to make a simple example just with the sort. Btw. you can remove many unnecessary annotations from the find method. Just @Param is mandatory — Simon Martinelli
– Simon Martinelli, Commented Feb 8, 2019 at 15:29

Selindek · Accepted Answer · 2019-02-09 13:15:16Z

The 'exported' name of your property is date_order. It should be used this way not only in json requests but also in request parameters.

@Column(name = "DATE_ORDER") @JsonProperty("date_order") private LocalDateTime orderDateTime;

Try this way:

http://localhost:8080/api/v1/orders/search/findByOrderDateTimeBetweenAndSearchTerm?dateFrom=2018-01-01T00:00:00&dateTo=2018-12-01T00:00:00&sort=date_order&searchTerm=O-2018

( sort=date_order )

I tried that but the query produced by Hibernate is still without the order by parameter.

Collectives™ on Stack Overflow

Spring Data REST: sort parameter is ignored

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related