Unable to replicate .skip(...)within Mongo repository method using @Query annotation?

[takanuva15]

Hi, we have some APIs on our server that allow the client to specify a custom skip and limit value for returning back the exact segment of results they want to see. We are using the Spring Data Mongo repository framework for implementing the queries on the backend. After a bunch of research, it doesn't seem possible to make the custom skip work with Spring repositories using the @Query annotation.

• I am aware of the Pageable construct, but this forces the slice of data that's returned to always be a multiple of the page size that's specified. Thus, if my page size is 50 but I want to return results from number 30-80, there is no way to be able to accomplish this straightforwardly using PageRequests - I would be forced to get 2 pages of data and then splice together the actual data the user is requesting.
• I am aware of the aggregation $skip operator, but Mongo already allows skipping on normal find queries without needing any aggregation pipeline.
• I am aware that I can make a custom repository fragment, manually implement my query, and invoke query.skip(...) in Java code. However, this is precisely what I want to avoid since the @Query annotation is already 95% the way there in generating this query declaratively.

I think the optimal solution to make the skip option in Mongo workable with the Mongo repository @Query framework is to add a new field initialOffset in AbstractPageRequest.java. That initialOffset would be between [0, pageSize), and it would get added on in the public long getOffset() calculation to decide the final offset. (In my example above, initialOffset would be 30, so with pageSize=50, page 0 would cover elements 30-79.)

If modifying AbstractPageRequest isn't possible, then perhaps we could somehow add a skip parameter to the @Query annotation and apply that to the query before the Pageable offset is added on. I did some poking around in the spring-data-mongodb codebase, and it seems that protected Object doExecute within AbstractMongoQuery.java does the magic for converting the @Query annotation into a normal Spring Query() object:

Query query = createQuery(accessor);

applyQueryMetaAttributesWhenPresent(query);
query = applyAnnotatedDefaultSortIfPresent(query);
query = applyAnnotatedCollationIfPresent(query, accessor);
query = applyHintIfPresent(query);
query = applyAnnotatedReadPreferenceIfPresent(query);

In addition, MongoQueryExecution.PagedExecution sets the skip on the query before the final execution. I'm not sure yet whether something can be hooked up between these, but it would only matter if we can't modify AbstractPageRequest.

Hopefully everything I said above made sense. Assuming this could be approved, I think I could work on a PR for this in the near future if the Spring team is too busy with other stuff.

Note: I saw this older issue "Add skip, limit and sort query feature to @Query annotation", but there is no solution in there to accomplish what the .skip() method does in normal Java code.

[christophstrobl]

Thanks for bringing this up. Let me take it to the team.

[mp911de]

We introduced meanwhile ScrollPosition (OffsetScrollPosition) and Limit types to allow for queries that are restricted by the notion of pagination. Check out Window scrolling queries and let us know whether this helps.

[takanuva15]

Thanks for the update! It seems like the OffsetScrollPosition would work, but could you share an example of how would I set that correctly if I wanted to get results from initialOffset=30 and pageSize=50? (The linked docs only show an example starting at 0)