Query with sort() and limit() in Spring Repository interface

Question

I'm new to Spring Data with MongoDB and would like to have an automagically generated query method inside my MongoRepository extension interface which requires filtering, sorting and limiting.

The query looks like this:

// 'created' is the field I need to sort against

find({state:'ACTIVE'}).sort({created:-1}).limit(1)

The repository interface looks like this:

public interface JobRepository extends MongoRepository<Job, String> {
    @Query("{ state: 'ACTIVE', userId: ?0 }")
    List<Job> findActiveByUserId(String userId);

    // The next line is the problem, it wont work since
    // it's not in the format @Query expects
    @Query("find({state:'ACTIVE'}).sort({created:-1}).limit(1)")
    Job findOneActiveOldest();

    ...
}

I know that one can add a Sort argument to a query method in order to get sorting but the problem is limiting the results to just a single object. Is this possible to do without having to write a custom JobRepositoryImpl?

Thanks

Edit:

Example of what I am looking for:

@Query("{ state:'ACTIVE', $orderby: {created:-1}, $limit:1 }")
Job findOneActiveOldest();

or

@Query("{ state:'ACTIVE' }")
@Sort("{ created:-1 }")
@Limit(1)
Job findOneActiveOldest();

But this obviously doesn't work :(

score 46 · Accepted Answer · edited Jul 21 '15 at 19:45

46

What's wrong with:

public interface JobRepository extends MongoRepository<Job, String> {

  @Query("{ state : 'ACTIVE' }")
  Page<Job> findOneActiveOldest(Pageable pageable);
}

and using it:

// Keep that in a constant if it stays the same
PageRequest request = new PageRequest(0, 1, new Sort(Sort.Direction.DESC, "created"));
Job job = repository.findOneActiveOldest(request).getContent().get(0);

edited Jul 21 '15 at 19:45

David

1,920
25
31

answered Apr 09 '12 at 18:02

Oliver Drotbohm

80,157
18
225
211

2

I was so busy looking for a short and simple query-like solution that I didn't bother to examine the Pageable class and realize that this in combination with Sort sort-of wraps the .sort() and .limit() functionalities of a mongo query. – m1h4 Apr 11 '12 at 07:34
4

I know this is the old thread, but is it possible to say in that case do not count the total number of items in the collection, because Page is returned and you do this under the hood (so there are 2 queries: one for data and one for count), I know about @QueryHints(forCounting = false) or return List instead of Page, but it works inside interface, is there any alternative of @QueryHints in case of repository.doSomething()? – Aliaksandr Kazlou Mar 15 '13 at 19:12
That only works for the 1st page. If the paging parameters are passed through from a controller, say, then the sort order may not always be maintained. There needs to be a way to bake it into the repository, same as when using Spring Data repository query method names (which just get too long to work with more complex filter criteria). But it appears to be the best you can do! – Charlie Reitzel Jun 21 '19 at 21:41
1

new PageRequest() is deprecated so i used PageRequest.of() – TimeTraveler Oct 06 '20 at 11:56
3

As @TimeTraveler mentioned new PageRequest() is now deprecated. Instead use `PageRequest request = PageRequest.of(0, 1, Sort.Direction.DESC, "created");` – Jamith NImantha Apr 14 '21 at 14:46

score 9 · Answer 2 · edited Jul 30 '14 at 15:15

9

Just adding a correction to Oliver's answer, it's Direction.DESC and not Directions.DESC and the order of the params is wrong.

Change :

PageRequest request = new PageRequest(0, 1, new Sort("created", Directions.DESC));

to:

PageRequest request = new PageRequest(0, 1, new Sort(Direction.DESC, "created"));

edited Jul 30 '14 at 15:15

David Arenburg

91,361
17
137
196

answered Jul 30 '14 at 14:23

Marc

582
2
7
14

9

This should be a comment and not an answer. – Honza Zidek Jul 30 '14 at 14:32
Or edit the original answer, but now that I think about it, new users don't have the rep to comment OR edit. – shoover Jul 30 '14 at 14:35
@Sinatr see [this discussion](http://meta.stackoverflow.com/questions/260245/when-should-i-make-edits-to-code) on meta, particularly the "Editing Code in Answers" section. TL;DR: SO is a reference resource, not a game; editing is encouraged if it produces improvement; all edits are logged and anyone can review history. – shoover Jul 30 '14 at 16:40
1

I need a reputaion of 50+ to comment on the answer I wanted to clarify, so please advise exactly how I was supposed to make this clarification. – Marc Jul 31 '14 at 20:39
Thanks! The code worked for me with a minor change: Pageable pageable = new PageRequest(0, 1, new Sort(new Sort.Order(Direction.ASC, "name"), new Sort.Order(Direction.DESC, "fID"))); – Simon May 14 '15 at 17:25
How to use above logic if I already have Page findOneActiveOldest(Pageable pageable); method implemented for another purpose, so implementing same will be Duplicate method in type VisitRepository. Please guide – Jun 23 '15 at 16:40

score 0 · Answer 3 · answered May 23 '23 at 18:02

0

Just to update the answer, in the latest version of springboot, this is how you will create the PageRequest object:

PageRequest request = PageRequest.of(0, 1, Sort.by(Sort.Direction.DESC, "created"));

answered May 23 '23 at 18:02

Arsalan Siddiqui

181
2
11

Query with sort() and limit() in Spring Repository interface

3 Answers3

Linked

Related