Add sorting/filtering/paginating of records based on AVA scenarios

[blcham]

The list of records visualized as a table might be quite long and ADMIN user typically needs to search those records based on specific scenarios (s1, s2, s3).

Motivation in AVA:

• there might be as many as 40000 records within ADMIN view of supplier's application
• (s1) Most of the time, the user wants to see the last modified records on top of the table
• (s2) Many times, the user wants to see on top only records in phase "open"
• (s3) ADMIN user would like to filter records by organizations

Possible future/optional requirements:

• it would be nice to have custom filtering/sorting persistent per user (at least while being logged in)
• it would be nice to have sorting on all the attributes
• In the long term, it would be nice to include filtering based on selected questions from a form (question is identified by origin)

Implementation proposal:

• records should be paginated
• headers should provide the possibility to sort alphanumerically
• it should be possible to select phase to filter

A/C:

• scenarios s1, s2, s3 are realizable in UI
• UI can handle 40000 records

[blcham]

@ledsoft

[ledsoft]

Did some research and found out the following:

• It makes sense to restructure the RecordDto returned by the backend API to save some data. That way, we can get to approximately 640B per record. Tested by generating a file with 40k records, it has about 36MB (with the reduced RecordDto). Which is quite a lot, but it can be gzipped to hundreds to KB (I gzipped mine to 155KB, but there were quite a lot of repetitions). GZipping can be configured either directly in Spring Boot or in nginx (which is recommended).
• Running some basic filtering on the 40k records in JS is quite fast, tens of ms at most
• Given these findings, it seems client-side filtering and paging could work just fine data-wise. The backend may sooner start hitting limits on the query processing and result loading if there are 40k records in the repository. In that case, loading records by using paging instead of all at once could be worth investigating.
• The main reason that could prevent doing filtering client side is whether export should work in concert with filters (that is, if filters are active, only matching records are exported) or whether all records are always exported. On the other hand, the export API already supports some basic filtering, so it would just be a question of using it and possibly extending it with additional filters (currently supported are min and max date, institution and phase).
• Another possible reason to do backend filtering could be if there are expected to be other services that may benefit from it.
• What would make sense even for client-side filtering is modifying the client not to fetch records every time the RecordsController is mounted either by checking whether records are already present in the client state (with the risk of showing stale data) or by using cache-based queries (If-Modified-Since header) which are, however, problematic when the backend is behind an Apache2 proxy.

[blcham]

I assume:

• by "restructure the RecordDto" you meant removing null values and user's detail as done in commit akaene/record-manager@8f5c574.
• backend filtering is much harder to implement than frontend filtering

Based on the assumptions, I suggest the following:

• "restructure the RecordDto" to reduce the size
• GZipping makes sense to me only if it does not complicate viewing HTTP requests in the developer's browser (maybe the browser handles it automatically?) and it is easy (< 2h) to implement on the frontend (as I understood that the backend requires only configuration in nginx)
• extending backend filtering is not needed. My reasoning -- I am not aware of other services besides export that could benefit from backend filtering. For the export, we might need in the future to implement filtering for a specific time period (start-date -- end-date), which I believe is already implemented on the backend. In case there is a need to implement export based on filters from frontend I would implement it in a way that the list of record IDs is used to the backend API instead of duplicating frontend filters on the backend. I assume that would not be that hard to implement.

The suggestion above does not handle filtering by form answer values. If we need it in the future, the backend API would have to be extended by filtering those values on the backend :( Including them in the record list as a flat list, not a question&answer model.

[blcham]

One more comparison:

• frontend

  • pros
    • we can provide offline support in the record list in case we migrate the app to PWA
  • cons
    • very hard to extend the support for filtering by answer values
    • filtered export or other future features dependent on filtered record list would have to be implemented by sending list of record IDs (will have to use POST instead of GET)

• backend

  • pros
    • easier to implement filtered export
    • easier to manage/test implementation of filtering in Java
  • cons
    • 1.5 bigger effort (by ML) + need to have optional in JOPA (Note, we will have to implement also pagination, sorting on the server, and sending possible sort criteria to the frontend)

[ledsoft]

Just to clarify, backend filtering does not require support for OPTIONAL in JOPA Criteria API. It would just make the code nicer...

[blcham]

After discussion, we chose to implement filtering at the backend.

[blcham]

This should not be closed yet.

[ledsoft]

@blcham

GZipping can be configured either directly in Spring Boot or in nginx (which is recommended).

https://docs.nginx.com/nginx/admin-guide/web-server/compression/