Combine two Pages with different Entities

I need to combine data from two different DTOs (or entities) into a single pageable result.

**The Problem **

I have two lists of DTOs, both of which are fetched separately from the database and paginated using Spring’s Pageable. The issue arises when I try to combine these two pageable lists into a single page object.

Here’s a specific example of the problem:

•   DTO 1: Suppose I have 10 total elements, with a page size of 5. This results in 2 pages.
•   DTO 2: Suppose I have 20 total elements, with the same page size of 5. This results in 4 pages.

When I attempt to combine these pages, I encounter strange side effects: If I request pages beyond what is available in DTO 1 (which has only 2 pages), I get sometimes more elements than expected are loaded or displayed.

It seems like combining pageable results from two different sources might be inherently problematic. Specifically, the differences in total elements and pages between the two DTOs seem to cause these issues.

Questions

1.  Is it problematic to combine pageable results from two different DTOs? Is this not how Pageable is intended to be used?
2.  Would the best approach be to refactor into a single query that combines the data from both DTOs into a custom object before applying pagination?
3.  Are there any best practices or patterns in Spring for handling such cases?

@GetMapping("/searchPaged")
@Operation(summary = "xxx")
public ResponseEntity<Page<?>> searchPaged(
        @RequestBody SearchCriteria criteria,
        @RequestParam int page,
        @RequestParam int size) {

    criteria.setSearchCategories(criteria.getSearchCategories() == null ? new ArrayList<>() : criteria.getSearchCategories());
    criteria.setSearchFields(criteria.getSearchFields() == null ? new ArrayList<>() : criteria.getSearchFields());

    int splitSize = size / 2;  // half page size cause of two pages
    Pageable pageableFirst = PageRequest.of(page, splitSize, Sort.by("first_field").ascending());
    Pageable pageableSecond = PageRequest.of(page, splitSize, Sort.by("second_field").ascending());

    Page<FirstDto> firstPage = firstSearchService.searchPaged(
            criteria.getProductId(),
            criteria.getSearchCategories(),
            criteria.getSearchTerms(),
            criteria.getSearchFields(),
            pageableFirst);

    Page<SecondDto> secondPage = secondSearchService.searchPaged(
            criteria.getProductId(),
            criteria.getSearchCategories(),
            criteria.getSearchTerms(),
            pageableSecond);

    List<Serializable> combinedContent = new ArrayList<>();
    combinedContent.addAll(firstPage.getContent());
    combinedContent.addAll(secondPage.getContent());


    long totalElements = firstPage.getTotalElements() + secondPage.getTotalElements();

    PageImpl<Serializable> combinedPage = new PageImpl<>(combinedContent, PageRequest.of(page, size), totalElements);



    return ResponseEntity.ok(combinedPage);
}

Solution

Is it problematic to combine pageable results from two different DTOs? Is this not how Pageable is intended to be used?

Yes, it is very problematic, especially if you introduce filtering. In your current example the user is expecting a page size of X items, but if one of your entity queries returns less records than the rest you will get pages that have half or less than the full page count. Generally this is only expected to happen on the last page, so it would be a strange user experience for it to happen in the middle of a larger set.

When filtering is applied, this will happen much sooner, if the page size is 10, and after filtering query 1 has 12 results and query 2 has zero results, then this will still require 3 pages of 5,5 and then 2 records.

Perhaps the more strange observations will be due to sorting, each set will be sorted, but if query 1 has say 15 records starting with A and query 2 only has 1, then the first 3 pages will have a mix of 5 records starting with A, while the second half of each page would start with different letters, it would look pretty wrong.

Skip counts and page numbers for individual records would also be a non-intuitive mess...

Would the best approach be to refactor into a single query that combines the data from both DTOs into a custom object before applying pagination?

Yes, however it may not need to be a custom object, if both entities are the same or same shape, then you should be able to union them together. Then sorting, filtering and paging will all behave as expected.

The downside to this is performance if your underlying store does not allow you union the queries themselves together and instead forces you to bring the records into memory.

Are there any best practices or patterns in Spring for handling such cases?

If you really need to combine multiple entities into a single paged request, then the standard approach is SQL Union: