It is possible to search data in calculated cells using datatable server-side ?

Anasito

Hi,

I'm using datatable with the server side, but the problem is that I can not use the search input to find the data in the calculated cells.
only data from the database can be found.

Can someone help me please ?

Anasito

here is a
screenshot of my datatable

kthorngren

With server side processing all searching is performed by the server script. Your server script would need to perform the calculations then use that as part of the search result. This SO thread shows one way to this might be done with a MySql query (if you happen to be using MySql):
https://stackoverflow.com/questions/16068662/calculated-column-in-where-clause-performance

Kevin

Anasito

thanks kthorngren for your answer.

the problem is more complicated yet. because the colum calculated is based from data in another table.

the filter you propose is based from one table

SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d

in my datatable i'm using for :
- Column Invoice n°, column invoice date and column client name are from table : "invoice"
- Column Amount is calculated from table : "product_invoice"
- Column Balance is calculated from table : "payement", "product_invoice" and "payement refund"

Search only vork for data from table invoice.

Anasito

hi,

I have various columns, each one of them includes data from different Tables.

In the classic mode of Datatables there is no problem to use search input to find identical word in all columns. There is listed all rows containing the searched word even if the tables are different.

But when using search input with server-side processing, only data from one table can be found. Its can work with columns that include data from multiples tables.

Thanks

colin

Hi @Anasito ,

As Kevin said, if serverSide is set, then the server script needs to do all the filtering. However, looking at your image, there are only a few hundred records in the table - server side processing is generally used when there are tens of thousands of records (as it is more efficient on those larger datasets). If that screenshot is typical of the number of expected records, then you could still use ajax to get the data from the server, but just use client-side processing (the default) for filtering/ordering/paging as the performance would be fine on the client with such a small dataset,

Cheers,

Colin

Anasito

Thank you for your answer Colin

The image posted is just given as an indication. I have lists with much more lines than those on the screenshot.

For the column “Amount” the solution that I found is to save it value in invoice table during the establishment of the invoice. Consequently, now the Column Invoice n°, column invoice date and column client name are from the same table ("invoice").
Result ==>> search is working for those 4 columns because they belong to the same table.

Remains the problem with the balance column that is calculated from multiple records on different tables ("payement", "product_invoice" and "payement refund").

thanks for your help and best regards