How to make searching, filter and paging into server side ?

[moschel26]

My application use CodeIgniter.

Library that I use is Ignited-datatables Library.

My controller is like this :

public function get_book()
{
$this->datatables->select('id, hotel, city, country, region')
->unset_column('id')
->from('hotel_book')
echo $this->datatables->generate('json', '');
}

My HTML is like this:

                <table id="example">
                    <thead>
                      <tr>                         
                        <th>Hotel</th>
                        <th>City</th>
                        <th>Country</th>
                        <th>Region</th>                                                   
                      </tr>
                    </thead>
                </table>

My Javascript is like this:

        var table = $('#example').dataTable( {


            "order": [[ 1, "asc" ]],

            "aoColumnDefs": [
                { 'bSortable': false, 'aTargets': [ 3 ]},
                { 'bSearchable': true }
            ],
            "Processing": true,
            "ServerSide": true,
            "sAjaxSource": '<?php echo site_url(); ?>book/get_book',
            "bJQueryUI": true,
            "sPaginationType": "full_numbers",
            "iDisplayStart ":20,
            "oLanguage": {
            "sProcessing": "<img src='<?php echo base_url(); ?>assets/images/ajax-loader_dark.gif'>"
            },      

            "columns": [
                    { "data": "hotel" },
                    { "data": "city" },
                    { "data": "country" },
                    { "data": "region" }
            ],  

            'fnServerData': function(sSource, aoData, fnCallback)
            {
                $.ajax
                ({
                    'dataType': 'json',
                    'type'    : 'POST',
                    'url'     : sSource,
                    'data'    : aoData,
                    'success' : fnCallback
                });
            }
        } );

Thank you.

[ghost]

Hey,

Which version of DataTables are you running?

The only thing that I can spot is "columns" param, try specifying "name" param within curly brackets.

"columns": [
{ "name": "hotel_book.hotel", "data": "hotel" },
{ "name": "hotel_book.city", "data": "city" },
{ "name": "hotel_book.country", "data": "country" },
{ "name": "hotel_book.region", "data": "region" }
],

Also it would be interesting to know on which library have you used to handle datatables in CI?

Vlad.

[moschel26]

@spikerok, Thank you for answering my question. Datatables version I use is DataTable version 1.10.4. While Ignited-Datatables version I use is Ignited-Datatables version 2.0.

[moschel26]

@spikerok, I tried to remove the function get_filtering in Ignited-Datatables Library and I adding new functions(function formatArraySearch) in the library like this : http://pastebin.com/gY85LhuQ. I changed the controller and javascript like this :
Controller : http://pastebin.com/4RWqbVsV.
Javascript : http://pastebin.com/7ULQNHYc

I'ts working. But I have difficulty in search by column

[ghost]

Try this library: http://pastebin.com/GreAs3tG

[moschel26]

It is not working. I tried searching, paging and order, but was not successful.
Controller : http://pastebin.com/5kyxBryx
Javascript : http://pastebin.com/7ULQNHYc

[ghost]

Library: http://pastebin.com/GreAs3tG
Controller: http://pastebin.com/kdeb6CMp
Example Database: http://pastebin.com/epQLhsvA

Result on screen:
Initial Screen

Searching for "M" and sorting by "City"

[moschel26]

@spikerok, Thank you very much for answering my question. I see his library there are two deficiencies

1. Search case sensitive
2. If the integer data type, searching does not work

[moschel26]

solved

In the datatables library, I replace the get filtering function to be like this :

private function get_filtering()
{

     ...

     $like .= $sColumns[$i] . " ::text iLIKE '%" . $ele . "%'";

     ...

}

It's working

Whether the solution is correct?

[ghost]

Hi.

1. Regarding search being case sensitive, which database and version do you have? By default LIKE '%%' is not case sensitive.
2. Regarding integer data type, i am not sure on how you have achieved this issue, could you give me an example please?

I have also played around today with the script and made small adjustments to get_filtering and get_ordering..

When you define datatables is JS you can specify which column within array that datatables receives data can be found for a particular cell and also you can specify which column in database should be used if you are to search / sort a particular column.

i.e.

"columns": [
            {"name": "jobs.id", "data": "identifier"}
        ],

"name" contains the column name that will be used to sort / search with. i.e. If type in search "100" my database query would be something similar to:

SELECT *
FROM jobs
WHERE jobs.id = "100"

"data" contains the key for data within array that datatables library returns. i.e. in controller we have a method that is called using AJAX

public function datatable() {
        $this->load->library('datatables');
        $this->datatables->select('id, hotel, city, country, region')
                ->unset_column('id')
                ->from('hotel_book');

        $data = json_decode($this->datatables->generate("json"), true);
        # loop through each item and set param identifier
        foreach ($data['data'] as $key => $ele) {
            $ele['identifier'] = $ele['id'] . '/' . $ele['city'];
        }
        # return array
        echo $this->datatables->generate('json', '');
    }

[ghost]

I have updated library online: http://pastebin.com/GreAs3tG

I have added global and local filtering.

[moschel26]

@spikerok, Hi
Thank you very much

1. I use PostgreSQL 9.4
2. My solution :

private function get_filtering()
{

 ...

 $like .= $sColumns[$i] . " ::text iLIKE '%" . $ele . "%'";

 ...

}

I've done the experiment and it worked

I modified the library datatables like this : http://pastebin.com/u30iAky0#

If you update the datatables library?

At that time, seemed the datatables library used : http://pastebin.com/qpKMLfa3

[ghost]

Ok, i see, we will need to use active record for queries and see how that works out.. i will try to update library sometime today and see what happens.

[moschel26]

@spikerok, Ok. Thank you very much

[ghost]

I didnt have a chance to look at this yet, but, i am not sure if active record can support complex queries, might need to have a interface to handle db queries.

[rooniieesss]

why i using $this->datatables->where ('work.work_id','VOID','!=');
it's show error "Uncaught Error: Call to undefined method Datatables::where() in".

anybody know about this problem? Thank you

[ghost]

@rooniieesss we would need to see how you are initiating datatables, possibly see some of your code. From your error message, it might be to do with your datatables class or with how you set it up.