Individual column searching (select inputs) + Ajax-ServerSide

Nicolas_Arias

Hello everyone, i have a problem implenting this filter. It only shows one page instead of all data.

Also i'm working laravel-adminlte

This's my index.blade.php (view)

@extends('adminlte::layouts.app')

@section('main-content')
    <!-- /*------------------CSS------------------*/ -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css">
    <!-- /*------------------CSS------------------*/ -->

    <!-- /*------------------Contenido------------------*/ -->
    <section class="content">
        <div class="row">
            <div class="col-xs-12">
                <div class="box">
                    <div class="box-header">
                        <h1 class="box-title"><b>Productos</b></h1>
                        @can('products.create')
                        <a href="{{ route('products.create') }}"><i class="btn btn-sm btn-success fa fa-plus-circle pull-right"> Crear</i></a>
                            <!-- <a href="{{  route('products.create')  }}" class="btn  btn-primary fa fa-plus-circle">Crear</a> -->
                        @endcan
                    </div>
                    <div class="box-body">
                        <table class="table table-responsive table-striped table-bordered" id="products">
                            <thead>
                                <tr>
                                    <th width="10px">ID</th>
                                    <th>Nombre</th>
                                    <th>Descripcion</th>
                                    <th>&nbsp;</th>
                                </tr>
                            </thead>
                            <tfoot>
                                <tr>
                                    <th width="10px">ID</th>
                                    <th>Nombre</th>
                                    <th>Descripcion</th>
                                    <th>&nbsp;</th>
                                </tr>
                            </tfoot>
                        </table>
                    </div> <!-- /.box-body -->
                </div> <!-- /.box -->
            </div>
        </div>
    </section>
    <!-- /*------------------Contenido------------------*/ -->

    <!-- /*------------------Script------------------*/ -->
    <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>

    <script>
        $(document).ready( function () {
            $('#products').DataTable({

                // "dom": '<"row"<"col-sm-4"l><"col-sm-6"f>>t<"col-sm-6"p><i>',
                "serverSide":true,
                "ajax": "{{ url('api/products') }}",
                "columns":[
                    {data: 'id'},
                    {data: 'name'},
                    {data: 'description'},
                    {data: 'btn'},
                ],

                initComplete: function () 
                {
                    this.api().columns([0,1,2]).every( function () //Columnas a mostrar
                    {
                        var column = this;
                        var select = $('<select><option value=""></option></select>')
                            .appendTo( $(column.footer()).empty() )
                            .on( 'change', function () {var val = $.fn.dataTable.util.escapeRegex($(this).val());
                            column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                        });
                        column.data().unique().sort().each( function ( d, j ) 
                        {
                            select.append( '<option value="'+d+'">'+d+'</option>' )
                        });
                    });
                }
                
            });
        });
    </script>
    <!-- /*------------------Script------------------*/ -->


    @endsection

And this's how it's shows

Hope you can help me

colin

Hi @Nicolas_Arias ,

The problem is that you're using serverSide processing - this means that only the rows being displayed are sent to the client, so when you build your dropdown list in the initComplete function, you will only ever see items sent for that very first page.

serverSide is only really needed if you have thousands of records, if you don't, and the dropdowns are more important, just remove that initialisation option.

Cheers,

Colin

Nicolas_Arias

Yes, i know this problem.

So, isn't exist a solution for this?

Anyways, thanks for your answer.

kthorngren

So, isn't exist a solution for this?

If you want to provide drop down search options using server side processing then you will need to provide a list of those options from your server script. You can do this through a separate ajax request. You might also be able to return the options in the Datatables ajax response and process them using the ajax.dataSrc as a function.

Otherwise, as Colin mentioned, the data for the drop down list is not available in the client data.

Kevin

Nicolas_Arias

Hm i don't know how to do that. Can you help me? or link some documentation?

Thanks for answer

kthorngren

Using server side processing then that typically means you have many thousands of rows of data. The first thing to decide is which columns to you want to provide a drop down list to search from, ie, how many unique data points are there in each column. Maybe text inputs would be a more suitable option for server side processing:
https://datatables.net/examples/api/multi_filter.html

Or maybe a combination of text inputs and drop down lists where appropriate.

If you still want to provide drop down lists then you will need a query in your server script to get the unique data for each column you want drop down lists. How you structure your query is dependent on your server code and DB. My suggestion would be to use an external ajax request to get the drop down list data. While this example is not one to generate drop down list data it does show this process. Its an example to build dynamic columns from the data retrieved.
http://live.datatables.net/qimukefe/1/edit

Your ajax request will go to a URL that would provide the drop down list data for each column. Then you would use the returned data in the initComplete function.

Kevin

Nicolas_Arias

I'm done, i couldn't make it. Even i tried make an invisible table.

I just don't know how edit that.

madrian

I have same problem as Nicolas. I have 5000 of record with images so I must use server side with pipelining example.

Database structure:

INDEX               NAME             WEEK

1                          A                  1/2020
2                          B                  1/2020
3                          C                  2/2020
4                          D                  3/2020
5                          E                  1/2020

I would like to create a selector with WEEK column from the database. It is somehow possible? Maybe first query WEEK column, populate selector and if user selects something send a new ajax query to query only matching data? If it is somehow possible can you please post a small example for the pipelining example above?

Thank you

kthorngren

@madrian Please don't duplicate your posts. I answered your same question in this thread:
https://datatables.net/forums/discussion/48930/individual-column-searching-select-text-inputs#latest

Kevin