Pagination multiple repositories

Question

I have two classess: A, B. For each class I have repository. I want display data in one table (frontend, something similiar to: http://embed.plnkr.co/V06RsBy4a6fShwmZcUEF/). I want have server side pagination.

List<Object> result = new ArrayList();
result.addAll(repoA.findAll());
result.addAll(repoB.findAll());

public interface repoAPaginationRepository extends PagingAndSortingRepository<A, Long> {
    Page<A> findAll(Pageable pageRequest);
}

public interface repoAPaginationRepository extends PagingAndSortingRepository<B, Long> {
    Page<B> findAll(Pageable pageRequest);
}

Is it enough to sum up the "count" from both repositories? Example: Repository A: 100 items, Repository B: 50 items. Sum: 150 items I want display 50 items per page.

I had the same problem to solve, with this I could make the union https://stackoverflow.com/a/55302501/5486945 — yOshi, Mar 22 '19 at 15:16

score 1 · Accepted Answer · answered Feb 16 '19 at 14:09

As you mentioned the count will be correct. you need to find a way to display the merged data correctly. We can see that your repositories sorts the records among there types. But if you concatenate the results, they will not be sorted.

In your example, lets suppose that repoA.findAll() return [7,8,9] and repoB.findAll() return [1, 100], the result [7,8,9,1,100] will not be sorted correctly. The solution you need depends on whether your data source (Database) supports an UNION operator or not

Using unions

JPA can not do this (union operation). But if your database provides an union operator (for example: SQL or mongoDB) you can use it to fetch the ids of the records according to the sort then fetch the records by ids through JPA.

No unions

If your database does not provide the, to do this, you will need to create a third repository, it must load 50 items from repoA considering an aOffset and 50 items from repoB considering a bOffset, then sort the 100 of them (it should be fast with merge sort and you can stop the algorithm at 50).

The code will look something like this

interface RepoA {
  List paginate(int count, int offset, SortCriteria sortCriteria);
}

interface RepoB {
 List paginate(int count, int offset, SortCriteria sortCriteria);
}

class RepoAB {
  private RepoA repoA;
  private repoB repoB;


  List paginate (int count, int offset, SortCriteria sortCriteria) {
     int aOffset = count == 0 ? 0 : calcAOffset(offset, sortCriteria);
     int bOffset = count == 0 ? 0 : offset - aOffset;
     return mergeSort(
             repoA.paginate(count, aOffset),
             repoB.paginate(count, bOffset),
             SortCriteria sortCriteria,
             50
           )
  }

  List mergeSort(List aList, List bList, SortCriteria sortCriteia, int stopAt) {
    ...
  }

  int calcAOffset (int offset, SortCriteria sortCriteria) {
    // This implementation can be very heavy, it will count all the records that
    // that appeared in the previous pages. 
    // You can evade this computation by knowing the offset using the last record 
    // in the previous page.
    return paginate(offset, 0, sortCriteria).filter(x => x instanceOf A).length
  }
}

Pagination multiple repositories

1 Answers1

Using unions

No unions