Rows listing endpoint on api/2 with filter, sort, search and paging #3095

Is your feature request related to a problem? Please describe.

In issue #3093 we added an endpoint to api/2 to fetch a row by id and uuid, but we didn’t add a listing endpoint yet in the same style. API v1 does offer such an endpoint, but it is limited in functionality. It doesn’t offer search, filters or totals calculation (so that clients can build paging)

The component store (sprint 1) and server-side paging in the data table (sprint 2) need exactly that functionality, also see the document in 12-rows-api.md

Describe the solution you'd like

We would start with adding the endpoint /api/2/{tables|views}/{nodeId}/rows, in the pattern of the RowOCSController with the existing RequirePermission read check on table or view. The apps/{application}/{table} code in issue #3094 will resolve to the same code.

The endpoint will accept the following parameters:

• page accepts only integers >= 1, and the api will return a 400 for anything that does not fit that. offset is an alternative to page but caculated to the corresponding page.
• limit with a default of 25 results, negative values are not accepted.
• sort: comma separated, minus for descending. Sort by the technical name, column id or meta column. Without _sort the default sort of the table or view is used. Use the existing tie-breaker on row id to keep pages stable.
• search: LIKE over text and selection cells and label search on selection via options. A better search follows with P9.
• fields: clamp the returned columns to the specified set (plus required metadata), but never more than the view’s columns.
• filter[{column}][{operator}] , where the [{operator}] part is optional. Filters rows by value in the column. When [{operator}] is not given, the behaviour is the same as when the operator is is-equal, so filter[name][is-equal]=jane has the same behaviour as filter[name]=jane . We follow the operator names as defined in the Row2Mapper. These filters are combined with AND. Filters also work on meta columns, these should not collide as technical names cannot contain capitals, and id or uuid should be reserved keywords.
  On multi-value columns, the non-operator filter should behave like the contains-item operator is set.

The view’s filter always applies, and the request filter is added with AND to each OR group of the view filter, so a request can never widen the view.

The response will look like this:

{
	results: [], // in current row shape plus the row's uuid
	total: 1, // the total number of rows found with the filter, found with SQL COUNT
	limit: 25, // the maximum amount of results per page
	page: 1, // the current page of the request
	pages: 1 // the total number of pages
}

Federated tables and views will for the time being give a clean error on this route. A named type for the paged response will be added to ResponseDefinitions.php and openapi.json will be generated.

Acceptance criteria:

• paging, sort, filter, search and fields work on both tables and views;
• a request filter on a view never returns rows outside the view;
• total matches the filter;
• limit 0, negative and page 0 or negative, give a 400, never "no limit";
• consecutive pages contain no duplicates and skip no rows;
• response times on the 100k benchmark table are measured for paging, a filter, sorting and search;
• CI is green on all four databases.

Describe alternatives you've considered

We considered listing without table/view in the /api/2/rows path: this is not offered for the same reason as in the uuid issue: without a table or view there’s no clear permission context.

Also a separate listing route with its own controller method was considered, we rejected this in favour of a second route definition on the method from the UUIDs for rows issue, so permissions and resolution are defined in the same place.

Also, we considered adding OR groupings in the query string: not in scope for this issue, possibly added later via its own issue.

Alternatively we considered a offset parameter instead of page but for consistency with general API behaviour we chose the page route. Also cursor based paging was considered, but that is not what components use. We do support offset, but we calculate that to be the page used.

Finally, search was considered to get a search index, but that is deferred to another issue.

Also, we considered filtering with the pattern ?{column}([{operation}])={value}, but this could collide with system parameters like sort. We could fix that with prefixing those system parameters with and underscore (_). We would prefer this as it is more consistent with general API behaviour for most APIs, but it differs from query parameters in existing API endpoints. If changing that is okay, we would like to follow this alternative.

Additional context

• Milestone M1, Sprint 1
• Rows API
• API index

Notes

• The rows API plan names "the twelve operators of the view filter"; the code currently has thirteen (Row2Mapper::getFilterExpression), including contains-item.
• The rows API plan describes item routes as /api/2/rows/{uuid}; following the UUIDs for rows issue, rows are addressed under their table or view instead, and this listing follows that pattern.
• If needed, a limit clamp of 500 results can be introduced to reduce server loads.

Relevant files:

• lib/Db/Row2Mapper.php (findAll, getWantedRowIds, addFilterToQuery, getFilterExpression, countRowsForView);
• lib/Service/RowService.php (findAllByTable, findAllByView);
• lib/Controller/RowOCSController.php (the existing api/2 row routes);
• lib/Db/Column.php (the meta columns: id, createdBy, updatedBy, createdAt, updatedAt);
• lib/ResponseDefinitions.php.

Related issues

This depends on the UUIDs for rows issue (the single-row route this listing extends, and the uuid per row). The collection route in the technical name issue builds on this listing, and the database indices for cell tables issue speeds up its filters. The object format for rows follows in a separate issue. Performance is measured on the 100 000 row benchmark table.