How to do a separate search on what the datatable shows and on the database?

sylarlocke

Hello, I am using datatables with the MVC Codeigniter 3 framework, with the option of server side, I have in each column, in the header, a search field, the search for these fields is done by sending a search for each of these fields to a query of the database that returns the information.

var table = $('#datatablemensual_ventas_servidor').DataTable({
    "responsive": true,
    "processing": true, 
    "serverSide": true,
    "fixedHeader": true,
    //"fixedColumns": true,
    //"autowidth": true,
        "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "Todos"]],
        "ajax": {
        "url": mostrarcontenido,//Cargamos nuestro contenido json en nuestra tabla
        "type": "POST",
        "data": function ( data ) {
          data.searchidpedido = $('#id_pedido').val();
          data.searchnum = $('#nºfact').val();
          data.searchfecha = $('#fecha').val();
          data.searchreferencia = $('#referencia').val();
          data.searchproducto = $('#producto').val();
          data.searchcantidad = $('#cantidad').val();
          data.searchpreciocosto = $('#preciocosto').val();
          data.searchtotalsiniva = $('#totalsiniva').val();
          data.searchtranspo = $('#transpo').val();
          data.searchcomi = $('#comi').val();
          data.searchbenefi = $('#benefi').val();
        }
    },

The point is that there are 3 fields, that the data is not in the database, but has been calculated when the information has been shown.

So what I would like to know is how I make the search for these three fields independent of the rest, so that when I do a search in one of these fields, it looks at what has already been shown in the datatables and not by sending a query.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

With serverSide, the query is always sent to the server, nothing is performed on the client. So it will be up to the script to return the values, and unless that script has knowledge of previous searches, it will need to issue it again.

Colin

sylarlocke

Thanks for answering and for your help, so I would like to know if there is any way to apply the searches.

The point is that one of these fields is an integer field, price.

At the time of displaying this price, a commission must be applied depending on the product, the transport and it is not obtained from any table or query, it is done in the controller before displaying the content and there the value to be obtained is applied. , so it won't find those values in the queries.

So I had thought that once the data has been displayed, what it has already displayed can be searched.

Thank you

colin

If you're not going to have many rows (say less that 50k records) - you can remove the serverSide, and that would mean the client would be search on the displayed data. The ajax would ensure the data is pulled from the server still, but the client does all search/paging/ordering,

Colin

sylarlocke

Thanks Collin, I have more than 500,000 records, what I am thinking of doing is before displaying the content in the datatable, save it in a temporary table and join this new table to collect the information.