8

Using the latest Spring Data Mongo (2.1.1 at time of writing), how do I specify to get the first record of a "custom" query method? Here is an example:

@Query(value="{name: ?0, approval: {'$ne': null}}",
        sort="{'approval.approvedDate': -1}",
        fields = "{ _id: 1 }")
List<Item> getLatestApprovedIdByName(String name, Pageable pageable);

/**
 * Finds the id of the most recently approved document with the given name.
 */
default Item getLatestApprovedIdByName(String name) {
    return getLatestApprovedIdByName(name, PageRequest.of(0, 1)).stream()
                                                                .findFirst()
                                                                .orElse(null);
}

Ideally I could just annotate getLatestApprvedIdByName taking only the String parameter.

There doesn't seem to be a limit field on the org.springframework.data.mongodb.repository.Query annotation.

It seems odd because I can emulate everything the named methods do except findFirst.

Without the Pageable, I get IncorrectResultSizeDataAccessException, and returning a List is not acceptable because I don't want to waste time returning an arbitrarily large result, plus the complicated code needing to deal with the possibility of 0 or 1 items.

ℛɑƒæĿᴿᴹᴿ
  • 4,983
  • 4
  • 38
  • 58
Jason Winnebeck
  • 1,254
  • 1
  • 10
  • 15
  • A few questions. Does your query return more than 1 items? And when is `IncorrectResultSizeDataAccessException` thrown? When you specify the method as `List getLatestApprovedIdByName(String name);`? – Sync Oct 24 '18 at 18:59
  • Yes, there are many documents that match the query. I want the latest approved one. If I could create a method like findFirstByNameOrderByApprovedDateDesc it would do what I want except the query and sort is too complicated to put in the method name as you can see in my example, since I'm looking at nested fields. – Jason Winnebeck Oct 24 '18 at 21:20
  • Does this answer your question? [Use limit and skip in MongoRepository](https://stackoverflow.com/questions/71887036/use-limit-and-skip-in-mongorepositorycustomer-string) – Étienne Miret Jan 26 '23 at 07:57

1 Answers1

11

Because your query returns multiple documents, there's no way to make it return a single Item directly.

Using Stream

// Repository
@Query(value="{name: ?0, approval: {'$ne': null}}",
        sort="{'approval.approvedDate': -1}",
        fields = "{ _id: 1 }")
Stream<Item> getLatestApprovedIdByName(String name);

// Service
default Item getLatestApprovedIdByName(String name) {
    return getLatestApprovedIdByName(name).stream().findFirst().orElse(null);
}

Due to the way Stream works, you'll only fetch the first query result instead of the entire result set. For more information, please see the documentation.

Using Page and Pageable

// Repository
@Query(value = "{name: ?0, approval: {'$ne': null}}", fields = "{ _id: 1 }")
Page<Item> getLatestApprovedIdByName(String name, Pageable pageable);

// Service
default Item getLatestApprovedIdByName(String name) {
    PageRequest request = new PageRequest(0, 1, new Sort(Sort.Direction.DESC, "approval.approvedDate"));
    return getLatestApprovedIdByName(name, request).getContent().get(0);
}

By making use of PageRequest, you can specify how many results you want as well as specify the sort order. Based on this answer.

Sync
  • 3,571
  • 23
  • 30
  • I tried Stream and I thought it didn't work. I will try again. I thought I saw it in the docs and wondered if it would really only pull the first item or if it would pull some window of items (the item documents are quite large and I've other methods). I will try this tomorrow. But this is a lot of code, at this point it almost seems easier just to use MongoTemplate and skip spring data repositories! I've certainly spent more time getting the "easy" way working over a MongoTemplate based DAO. – Jason Winnebeck Oct 25 '18 at 01:19
  • 1
    I tried this and it did functionally work but not by performance. After I made the change from List to Stream (and I kept the Pageable), the response time for the REST endpoint that directly proxies that method went from 8ms to 4.7s (I tested multiple times and servers). This is odd because even though the limit was left off the query the production of _id field only stayed and test database only has a few documents in the collection. So, I will keep to the List method. As it sounds like the answer is you can't limit via @Query annotation alone I can mark answered. – Jason Winnebeck Oct 25 '18 at 13:05
  • I want to know stream and page which one is faster. I hope @Query support limit in the future. The most performance way right now is to write a Query with query.limit() – Maxi Wu Dec 19 '19 at 02:49