Search

[illuminate3]

Search is giving me this error

DataTables warning: table id=table - Ajax error. For more information about this error, please see http://datatables.net/tn/7

here is my view call:

$(document).ready(function() {
oTable =
    $('#table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "{{ URL::to('admin/api/job_titles') }}",
        "columns": [
            {data: 'name', name: 'name'},
            {data: 'description', name: 'description'},
            {data: 'updated_at', name: 'updated_at'},
            {data: 'actions', name: 'actions'}
        ]
    });
});

Everything is running good!

[yajra]

I think this was due to the number of columns did not match the th fields in your table. Can you please double check if your table has the same header as defined in your columns declaration or paste it here?

[illuminate3]

Everything shows fine. It's just that the search shows that error.

here"s my view:

<div class="row">
<table id="table" class="table table-striped table-hover">
    <thead>
        <tr>
            <th>{{ trans('kotoba::table.name') }}</th>
            <th>{{ trans('kotoba::table.description') }}</th>
            <th>{{ trans('kotoba::table.updated_at') }}</th>

            <th>{{ Lang::choice('kotoba::table.action', 2) }}</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>
</div>

I'll run a check without the language translations. However, I don't think that is an issue....

[yajra]

I see. Since your using action field and it's not searchable because it's computed, you need to declare it like below: You need to remove the name attribute and optionally add something like orderable and searchable.

{data: 'actions', orderable: false, searchable: false}

[yajra]

BTW: you can also use firebug / inspect element on Chrome to better trace the error.

[illuminate3]

I was removing the ID field

        $query = Site::select('id', 'name', 'division_id', 'website', 'user_id');

        return Datatables::of($query)
//          ->remove_column('id')

I use the ID field for the actions (view, edit, delete).
If I comment out the remove_column(ID) like above. Everything works.

I still need to stress test.
Note: DataTables-1.10.5 works with no problems.

[illuminate3]

More not so good news

        $query = Employee::join('profiles','employees.user_id','=','profiles.id')
            ->select(array('employees.id','profiles.first_name','profiles.middle_initial','profiles.last_name','profiles.email_1'));

Will also cause a search error.

This is ok

        $query = Site::select('id', 'name', 'division_id', 'website', 'user_id');

@ stress test
I still haven't tried a complicated join statement but a simple select on 12,000 records is almost as fast as I was getting with chumper.

[yajra]

Thanks will try to do more testing too with joins on my free time. I think the original version (bllim) were also having this issues. But so far on my projects it does works well.

[illuminate3]

The search errors are with the parsing of the datatables and not on the laravel side. I can echo out the api call with no problems.

Could you post some of your code?

[illuminate3]

I really didn't like the blim version and chumper's version isn't as simple as your's. IF you changed the name of this package I bet a lot of people would look at it.

[yajra]

Here's a sample code on my project with joins using Laravel 4:

public function getDataTable($id=0)
{
    $models = $this->model->select('sales.*','carriers.name as carrier',
        'leads.first_name','leads.last_name', 'sale_statuses.status','products.name as product')
    ->leftJoin('leads','leads.id','=','sales.lead_id')
    ->leftJoin('sale_statuses','sale_statuses.id','=','sales.status_id')
    ->leftJoin('carriers','carriers.id','=','sales.carrier_id')
    ->leftJoin('products','products.id','=','sales.product_id');

    // display sales for specific lead
    if ($id) $models->where('lead_id', $id);

    return Datatables::of($models)->make(true);
}

salesTable = $('#sales-table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "{{ url('admin/sales/data-table/'. $lead->id) }}",
        "columns": [
            { "data": function(d){
                return '<input type="checkbox" name="id[]" value="'+ d.id +'" />';
            }, "orderable": false, "searchable":false, "name":"sales.id" },
            { "data": function(d){
                return d.lead_id ? '<a href="{{ url("admin/leads/show") }}/' + d.lead_id + '#sales">' + d.first_name + ' ' + d.last_name + '</a>' : '';
                }, "name": 'leads.first_name', "visible": {{{ $lead->id ? 'false':'true' }}}
            },
            { "data": function(d){
                return '<a href="{{ url("admin/sales/edit") }}/' + d.id + '">' + d.product + '</a>';
                }, "name": 'products.name'
            },
            { "data": "appDateFormatted", "name":"sales.application_at" },
            { "data": "carrier", "name":"carriers.name" },
            { "data": "amount", "name":"sales.amount" },
            { "data": "application_no", "name":"sales.application_no" },
            { "data": "status", "name":"sale_statuses.status" }
        ],
        "order": [[3, 'desc']]
    })

Thanks for the appreciation for this package. I originally fork this version because bllim's version is not working on oracle and since Laravel does not support Oracle too, I was hesitant to make PR to bllim to support oracle so I decided to create my own and specifically target Oracle users. But this project does work on other database too just as the original fork.

[illuminate3]

Thanks!
This has helped a lot!

OK, what I'm noticing as errors so far.

DB::raw('CONCAT(first_name, " ", last_name) AS full_name'),

Will display but will cause error on search: because full_name is not a column name.

Concating at the view level:

            {
                data: function(d){
                    return d.last_name + ', ' + d.first_name;
                },
                name: 'last_name',
                orderable: true,
                searchable: true
            },

displays no problem and will search on last_name with no issues. However! this method will not let you search on first_name. So close ... 😭

If you build your actions in the controller, like I did above, make sure to add the orderable and searchable values in the js: id

            {
                data: 'actions',
                name: 'actions',
                orderable: false,
                searchable: false
            }

I should start a new issue for this but

{ "data": "carrier", "name":"carriers.name" },

Why is "carriers.name" under the "name" option and not the "data" js option?

thanks~!~

[yajra]

For carriers.name, since I am using a join statement. We need to add the table prefix on the name on js since on v1.10++ we use the name defined in the javascript as a reference for searching in the server side. This will avoid the ambiguous column name issue.

If you want to search for first_name you can add a column on datatables and set it as visible:false. That's what I usually do atm.

{data: 'first_nam'`, name: 'first_name', visible: false}

[illuminate3]

That works!
Thanks!

note:
add a blank set of TH to html table. I was getting errors with out them.

<th></th>

[gadius]

@mastrip2 Just nailed it; changed my initialization as follow.

var tableIndex=$('#tableIndex').DataTable({
"order": [[ 0, "asc" ]],
"aLengthMenu": [[5, 10, 25], [5, 10, 25]],
"iDisplayLength": 10,
"dateFormat": 'yyyy-mm-dd',
"processing": true,
"serverSide": true,
"ajax": {
'url': '{{route("clinics.api.laboratoryIndex")}}',
'type': 'POST',
'headers': {
'X-CSRF-TOKEN': '{{ csrf_token() }}'
}
},
"columns":[
{data: 'products', name: 'products'},

],
"columnDefs": [
{ "searchable": false, "bSortable": false, "targets": 0 }
],
language: {
"sProcessing": "Procesando...",
"sLengthMenu": "Mostrar MENU registros",
"sZeroRecords": "No se encontraron resultados",
"sEmptyTable": "Ningún dato disponible en esta tabla",
"sInfo": "Mostrando registros del START al END de un total de TOTAL registros",
"sInfoEmpty": "Mostrando registros del 0 al 0 de un total de 0 registros",
"sInfoFiltered": "(filtrado de un total de MAX registros)",
"sInfoPostFix": "",
"sSearch": "Buscar: ",
"sUrl": "",
"sInfoThousands": ",",
"sLoadingRecords": "Cargando...",
"oPaginate": {
"sFirst": "Primero",
"sLast": "Último",
"sNext": "Siguiente",
"sPrevious": "Anterior"
},
"oAria": {
"sSortAscending": ": Activar para ordenar la columna de manera ascendente",
"sSortDescending": ": Activar para ordenar la columna de manera descendente"
}
}
});

Thank you fam