How to do Paging based on dynamic validation criteria?

Question

Here is the case:

I need to display a list of records on a page, and paging is necessary. The question I got is whether a record should be displayed depends on validation result computed in memory, after selected from database.

for example, 50 records for one page:

1. Select 50 records from database
2. 30 records are left after validation

Solution I have now it get all records from database, do validation and then get valid list of records. Paging is based on this list.

Is there any other good solution for this?

Answer 1

In the optimal case pagination can be divided in two steps. In the first step according to the selecting query a set of rows is selected from a database. All these rows could be displayed. Instead of fetching actual rows just retrieve a list of their identifiers. This list however large can be usually kept in memory. Second step is paging through the list by asking for n-th page of m items. Then only m rows are being completely retrieved from the database using their ids.

Additional step of computation is negating the idea of pagination that is having a list of identifiers of the whole result set.

What I can think of now without seeing the computation is to store results of computation in the database whenever a displaying row is being inserted/updated in the db. Since computation results depend from input parameters then for every row and for every range of input parameters you could have a different result.

This would then make paging possible. Performing the first step of paging should now include precomputed validation results and provide much faster retrieval of the list of row ids.