1

How can I use Spring Data JPA to find results in two repositories using Pageable? Consider that we have 2 repositories: the first repository has 5 rows matching a certain condition. The second repository has 12 rows matching the condition. Sending a request for page 0 with size 10 should retrieve the 5 rows from the same repository and 5 rows from the second repository. Sending a request for page 1 with size 10 should retrieve the remaining 7 rows from the second repository. The Page result must return the correct number of total rows, and, if possible, the results from different pages should not overlap.

This is what I've tried:

Page<Order> orders = firstRepository.findByCondition(page)
        .map(mapper::entityToPojo);
if (orders.getNumberOfElements() >= page.getPageSize())
    return orders;

PageRequest pageRequest = PageRequest.of(page.getPageNumber(), page.getPageSize() - orders.getNumberOfElements());

Page<Order> otherOrders = secondRepository.findByCondition(pageRequest)
        .map(mapper::otherEntityToPojo);
List<Order> combinedOrders = Stream.of(orders.getContent(), otherOrders.getContent())
        .flatMap(List::stream)
        .collect(Collectors.toList());
return new PageImpl<>(combinedOrders, page, orders.getTotalElements() + otherOrders.getTotalElements());

It works fine for the first page, but for the second page and so one, since it queries for page with index 1, it retrieves only 2 results from the second repository, considering that the other 10 were fetched on page with index 0.

I tried other combinations also, but this is the closest one to the truth. Thank you in advance.

Saron Ciupe
  • 11
  • 2
  • You cannot as that is not how paging works. They are apparently the same objects/types else you couldn't return them. Write a single query (or repository) that retrieves all in one pass instead of multiple passes, then apply paging to that single query. – M. Deinum Mar 13 '23 at 14:50
  • `PageRequest` does not allow offsets with finer granularity than one page. You will need to use a different implementation of `Pageable`. I am not seeing one jump out that will work for you, so you may end up having to implement a custom `Pageable` – egeorge Mar 13 '23 at 22:22

0 Answers0