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 :)

Have you tried it without a `@Query`? Is the finder matching your domain object and are yo using a Spring Data version that has support for the `Top` keyword? — M. Deinum, Sep 02 '15 at 11:43
for starters your parameters are in the wrong order... And please add the code of the `@Query`. — M. Deinum, Sep 02 '15 at 12:06

xenteros · Answer 1 · 2017-09-28T17:49:11.680

10

The method to resolve your problem is:

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

Top10 limits the results to first 10.
ByDomainId limits results to those that have passed domainId.
And adds another constraint.
CategIdIn limits results to those entries that have categId in the passed List.
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.

edited Sep 28 '17 at 17:49

answered Sep 28 '17 at 17:42

xenteros

15,586
12
56
91

7

Is is possible to create method for top n where `n` could be passed as parameter? For e.g. `findTopNByEmailOrderByIdDesc(String email, Long n)` @xenteros – Ravi Desai Aug 19 '20 at 12:07
It is documented that we can use `top` parameter and it works without Order desc, but fails with order by desc. https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result – Dmitriy Popov Dec 19 '20 at 02:58
This behaviour seems to be bugged, see my issue — https://stackoverflow.com/questions/65366365/spring-repository-auto-generated-method-select-by-given-top-number-order-by-f – Dmitriy Popov Dec 19 '20 at 03:33
@DmitriyPopov that's not bugged. It's because there is no `by` between field and desc. `orderByFieldDesc` not `orderByFieldByDesc` – xenteros Dec 19 '20 at 12:00

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

1 Answers1