Column list selection for DataTables API

[grakic]

Add support for column list selection using DataTables server-side API.

DataTables API is passing a list of columns (columns[i]) that may not match with the table definition on the server. For each request we build a list of columns as requested by the client, then use this list for building query. Columns with data source set to null or undefined are ignored, but searching and ordering column indexes are mapped to the column list in request. Returned results are labeled to match the request.

Returning nested data sources is not supported.

[codecov-io]

Codecov Report

Merging #86 into master will increase coverage by 0.26%. The diff coverage is 96.29%.

@@            Coverage Diff             @@
##           master      #86      +/-   ##
==========================================
+ Coverage   93.68%   93.95%   +0.26%     
==========================================
  Files           1        1              
  Lines         190      215      +25     
==========================================
+ Hits          178      202      +24     
- Misses         12       13       +1

Impacted Files	Coverage Δ
datatables/__init__.py	93.95% <96.29%> (+0.26%)	:arrow_up:

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update 6d53407...c6ed714. Read the comment docs.

[tdamsma]

@grakic, please forgive my ignorance, but can you explain to me what the benefit of this is? Could you select a subset of the columns client side and only have those returned?

It would be really helpful if you could also add such functionality in the examples, so it is immediately clear what the benefits are

[tdamsma]

@Pegase745 What do you think of this PR?

[Pegase745]

I agree with @tdamsma on this one. I read it over and over and don't seem to catch the use case where this is needed. @grakic possible to link an example, or explain one at least please ?

[grakic]

Datatables server side API is expecting either arrays or objects. When passing objects, data/mData field is used for matching columns to the data source. This allows one to decouple server side code from the front end code. ( https://datatables.net/examples/ajax/objects.html )

In the frontend columns can be defined differently (reordered, or with some columns hidden).

If we now read server side documentation ( https://datatables.net/manual/server-side ) we see that Datatables is sending list of columns in the columns[i][data] array. Search and order options are then given relative to the column's position in this list (request), not relative to the column's definition in the backend.

Without this patch, sqlalchemy-datatables is expecting that Datatable is defined the same in the frontend and in the backend, and that all columns are used. In that simple case columns[i][data] can be ignored.

But when frontend is using a subset of backend's column, or using them in a different order, we need to apply order and search parameters relative to the request, looking at colums[i][data] parameters, and not column's index in the backend code.

I have several Datatables with user adjustable set of columns, each receiving data from the same backend endpoint. In the backend for each data request I need to correctly apply search and order parameters. This is what Datatables expect from server-side code and what is documented in the manual.

As a bonus value we can also not query extra columns, returning just the selected subset. When new columns are added, Datatables would repeat request with those new columns included so everything works as expected. It can be debated if we should always return all data (allowing caching) or not. Datatables manual is not specific on this. My use case is a Datatable with many calculated columns (some running extra subqueries), so I would rather fetch just the requested subset. It also makes implementing previous requirement trivial as we can just create a new list of columns matching request in _set_column_list() and run other code as usual. Otherwise, both filter and sort code would need to be updated.

[jserafim]

I just started using this project, and noticed that the current implementation doesn't follow the DataTables documentation.

As a simple example, if my API returns an object with fields id and name, but the front-end only displays the field name, sorting by name doesn't work unless the id field is declared last in the column list (on the server). If id is declared first on the server, the front-end requests column name and sorting by the first column, which should be name, but the result is sorted by id.

DataTables is already sending a list of columns in the request, and this list order must be followed by the server side code.

Search and order options are then given relative to the column's position in this list (request), not relative to the column's definition in the backend.

DataTables documentation:

Column to which ordering should be applied. This is an index reference to the columns array of information that is also submitted to the server.

I don't have a strong opinion about the data that should be returned, and DataTables documentation is not clear on this. If the server returns a subset of columns, there's always the option of adding any required column in the front-end, and maybe using "visible" = false. If it returns all data, there's the option of implementing another API endpoint for a specific use case.