spring crud repository find top n Items by field A and field B in list order by field C

Question

I have in a Spring Repo something like this:

findTop10ItemsByCategIdInOrderByInsertDateDesc(List ids)

I want the first 10 items where category id in list of ids ordered by insert date.

Another similar query:

findTop10ItemsByDomainIdAndCategIdInOrderByInsertDateDesc(List ids, @Param Integer domainId)

Here I want that the domain id is equal to the given param and the categId to be in given list.

I managed to resolve it using @Query but I wonder if there is an one liner for the above queries.

thanks

EDIT The top works fine. Initially I had findTop10ItemsByDomainIdAndCategIdOrderByInsertDateDesc. Now I want the results from a list of category ids. That's the new requirement.

SECOND EDIT My query works for find the set o results where domain id is equal to a given param and categ id is contained in a given list. BUT I found out that HQL doesn't support a setMaxResult kind of thing as top or limit.

@Query("select i from Items i where i.domainId = :domainId and i.categId in :categoryIds order by i.insertDate desc")

The params for this method were (@Param("domainid") Integer domainid,List<Integer> categoryIds) but it seams that I'm alowed to use either @Param annotation to each parameter or no @Param at all ( except for Pageable return; not my case )

I still don't know how to achieve this think: extract top n elements where field a eq to param, field b in set of param, ordered by another field.

ps: sorry for tags but there is no spring-crudrepository :)

Answer 1

The method to resolve your problem is:

List<MyClass> findTop10ByDomainIdAndCategIdInOrderByInsertDateDesc(Long domainId, List<Long> ids);

1. Top10 limits the results to first 10.

2. ByDomainId limits results to those that have passed domainId.

3. And adds another constraint.

4. CategIdIn limits results to those entries that have categId in the passed List.

5. OrderByInsertDateDesc orders results descending by insert date before limiting to TOP10.

I have tested this query on the following example:

List<User> findTop10ByEmailAndPropInOrderByIdDesc(String email, List<Long> props);

Where User is:

private Long id;
private String username;
private String password;
private String email;
private Long prop;

Currently I would recommend using LocalDate or LocalDateTime for storing dates using Spring Data JPA.