How to do filtering on datatables ignoring uppercase and lowercase?

Question

How to do filtering on datatables ignoring uppercase and lowercase?

Navigation

#1 by (1 votes)

0

I have a table made with server-side datatables but at the time of filtering, eg: "ing" ignores that "Engineer" exists, that is, it takes into account the case. How can I avoid that?

Here is the code of my table:

var table = $('#table').DataTable({
    "destroy": true,
    "responsive": true,
    "processing": true,
    "serverSide": true,
    "ajax": "tabla/cargos",
    "columns": [
        {data: 0, searchable: false, orderable: false, render: function( data, type, full, meta ){
                return meta.row+1;
            }
        },
        {data: 1},
        {data: 2, 
            render: function( data, type, full, meta ){
                if (data) {
                    return '';
                }else {
                    return '';
                }
            }
        },
        {data: 3, searchable: false, orderable: false},
    ],
    "fnDrawCallback": function() {
        $("[name='my-checkbox']").bootstrapToggle();
    },
    order: [[1, 'asc']],    
    "language": {
        "url": "js/idioma_espaniol_datatables.js"
    },
    initComplete: function (data) {

        var column1 = this.api().column(1);
        $('#filter_nombre').keyup(function() {
            var val = $.fn.dataTable.util.escapeRegex(
                $(this).val()
            );
            column1.search(val).draw();
        });

        var column5 = this.api().column(2);
        $('#estados').on( 'change', function () {
            var val = $.fn.dataTable.util.escapeRegex(
                $(this).val()
            );
            column5.search(val).draw();
        });
    }
});

Update 1:

public function tabla(){
    try{
        $sql = Cargos::select(array('id', 'nombre', 'status'));
        return Datatables::of($sql)
        ->addColumn('action', ' Editar  Eliminar')
        ->make();
    }catch(\Illuminate\Database\QueryException $e){
        $array = array(
            'mensaje' => $e->getMessage(),
            'codigo' => $e->getCode(),
            'sql' => $e->getSql(),
            'bindings' => $e->getBindings(),
        );
        return Response::json(array('ErrorSql' => $array));
    }
}

datatables postgresql postgresql-9.3

asked by Pablo Contreras 09.01.2017 в 21:33

source

1 answer

Something similar to XAMPP to use with Electron? Send an image using JSON in node.js (express) to an android device

score 1 · Accepted Answer

As the comment of @MarcosGallardo says,

The problem is in your backend

Investigate how to do it on the server side.

Here is how it is created and your query with ajax:

var table = $('#table').DataTable({
    "filter": false,
    "destroy": true,
    "responsive": true,
    "processing": true,
    "serverSide": true,
    "ajax": {
        url: 'tabla/cargos',
        method: 'POST',
        data: function (d) {
            d.nombre = $('input[name=filter_nombre]').val(); //campo del cual obtendremos el dato a filtrar
        }
    },
    "columns": [
        {data: 0, searchable: false, orderable: false, render: function( data, type, full, meta ){
                return meta.row+1;
            }
        },
        {data: 1},
        {data: 2, 
            render: function( data, type, full, meta ){
                if (data) {
                    return '';
                }else {
                    return '';
                }
            }
        },
        {data: 3, searchable: false, orderable: false},
    ],
    "fnDrawCallback": function() {
        $("[name='my-checkbox']").bootstrapToggle();
    },
    order: [[1, 'asc']],    
    "language": {
        "url": "js/idioma_espaniol_datatables.js"
    },
    initComplete: function (data) {
        $('#filter_nombre').keyup(function(e) { //aca el evento keyup para que filtre cada vez que presiones una tecla.
            table.draw(); //y aca ejecuta de nuevo la tabla y llamara el valor del campo.
            e.preventDefault();
        });
    }
});

On the server side:

public function tabla(Request $request){ //tiene que tener el request para obtener los campos que se estan enviado de la vista para aca
    try{
        $sql = Cargos::select(array('id', 'nombre', 'status'));
        return Datatables::of($sql)
        ->filter(function ($query) use ($request) {
            if ($request->has('nombre')) { //verificamos que venga el campo
                $query->where('nombre', 'ilike', "%{$request->get('nombre')}%"); //el "ilike" es el que consultara obviando las mayusculas y minusculas
            }
        })
        ->make();
    }catch(\Illuminate\Database\QueryException $e){
        $array = array(
            'mensaje' => $e->getMessage(),
            'codigo' => $e->getCode(),
            'sql' => $e->getSql(),
            'bindings' => $e->getBindings(),
        );
        return Response::json(array('ErrorSql' => $array));
    }
}

Source page