Laravel-datatables: Search Option not working when I used Join Query.

[infoshahin]

When I add join query Like that :

$Employee = Employee::leftJoin('projects','employees.type_id','=','projects.id')
->select(array('employees.name','employees.type','projects.project_name','employees.join_date','employees.comment','employees.id AS eid'));
return Datatables::of($Employee)
->edit_column('eid','


')
->make(true);

Datatable work fine as well, But when I press the any key at search box it's showing error!!

The error is :

" QueryException in Connection.php line 624:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'eid' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as row_count from employees left join projects on employees.type_id = projects.id where (LOWER(name) LIKE %t% or LOWER(type) LIKE %t% or LOWER(project_name) LIKE %t% or LOWER(join_date) LIKE %t% or LOWER(comment) LIKE %t% or LOWER(eid) LIKE %t%)) count_row_table) "

Where is my mistake I can't understand it. How can I fix the problem.

[yajra]

can you please paste your js declaration? If your using the latest version you should define the name: table.column pattern.

[infoshahin]

Tnq for suggestion, I already solved the problem! Now js declaration something like that , it works for me :
oTable = $('#employees').DataTable({
"processing": true,
"serverSide": true,
"ajax": "{{ URL::to('/employeesdata') }}",
});
I followed bllim Laravel DT bundle system.

[yajra]

Yes, this package was originally forked from Bllim and some function should work the same. However, some of the updates on Bllim is not implemented here and/or I do have my own version of implementation. So kindly refer to the read me page for basic usage info.

Also, don't forget to check out datatables.net documentation.

[infoshahin]

Ok, I will follow your Instruction. You are so genius Bcz you created this plugin for help us. Thank you.

[omar-faruk-sust]

same exception i have found for the join query. Search option is not working. here is a problem of with the same column "name". in the user table i have the there is a column with "name" and also in user_role table.

$users = DB::table('users')
            ->join('role_user', 'users.id', '=', 'role_user.user_id')
            ->join('roles', 'roles.id', '=', 'role_user.role_id')
            ->select('users.*', 'roles.name as role'); 

QueryException in Connection.php line 624: SQLSTATE[23000]: 
Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous 
(SQL: select count(*) as aggregate from (select '1' as row_count from `users` inner join `role_user` on `users`.`id` = `role_user`.`user_id` inner join `roles` on `roles`.`id` = `role_user`.`role_id` where (LOWER(`avatar`) LIKE %sal% or LOWER(`name`) LIKE %sal% or LOWER(`email`) LIKE %sal% or LOWER(`role`) LIKE %sal% or LOWER(`created_at`) LIKE %sal% or LOWER(`updated_at`) LIKE %sal%)) count_row_table)

[yajra]

You should use fully qualified column name on your js. Follow these demo http://datatables.yajrabox.com/fluent/joins

[omar-faruk-sust]

i have followed your convention but same error. :(

$users = DB::table('users')
            ->join('role_user', 'users.id', '=', 'role_user.user_id')
            ->join('roles', 'roles.id', '=', 'role_user.role_id')
            ->select('users.id','users.name','users.email','users.avatar','users.created_at','users.updated_at', 'roles.name');
        //dd($users);
        return Datatables::of($users)
            ->addColumn('action', function ($user) {
                $actions = '';
                if (Auth::user()->can('view-users')) {
                    $actions .= '<a href="users/' . $user->id . '" class="btn btn-xs btn-info"><i class="fa fa-eye"></i> View</a>';
                }
                if (Auth::user()->can('edit-users')) {
                    $actions .= '  <a href="users/' . $user->id . '/edit" class="btn btn-xs btn-primary"><i class="fa fa-pencil-square-o"></i> Edit</a>';
                }
                return $actions;
            })
            ->editColumn('users.avatar', function($user) {
                if(empty($user->avatar)) {
                    return '<img src="'.GlideImage::load('skins/adminLTE/dist/img/default-avatar.jpg')->modify(['w'=> 45, 'h' =>45, 'fit' => 'crop']).'" class="img-circle">';
                }
                return '<img src="'.GlideImage::load('/files/avatar/'.$user->avatar)->modify(['w'=> 45, 'h' =>45, 'fit' => 'crop']).'" class="img-circle">';
            })
            ->editColumn('users.created_at', function ($user) {
                return $user->created_at ? with(new Carbon($user->created_at))->format('m/d/Y') : '';

            })
            ->editColumn('users.updated_at', function ($user) {
                return $user->updated_at ? with(new Carbon($user->updated_at))->format('Y/m/d') : '';
            })
            ->make(true);

<script type="text/javascript">
        $(document).ready(function() {
            $('#users-table').DataTable({
                processing: true,
                serverSide: true,
                ajax: '{{ route("admin.ajax.users.list") }}',
                columns: [
                    {data: 'avatar', name: 'users.avatar' },
                    {data: 'name', name: 'users.name'},
                    {data: 'email', name: 'users.email'},
                    {data: 'role', name: 'roles.name', searchable: false},
                    {data: 'created_at', name: 'users.created_at'},
                    {data: 'updated_at', name: 'users.updated_at'},
                    {data: 'action', name: 'action', orderable: false, searchable: false}
                ]
            });
        });

    </script>

[yajra]

Follow the js convention. {data: 'title', name: 'posts.title'}, Notice posts.title is added on name?

    $('#posts-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: 'http://datatables.yajrabox.com/eloquent/joins-data',
        columns: [
            {data: 'id', name: 'posts.id'},
            {data: 'title', name: 'posts.title'},
            {data: 'name', name: 'users.name'},
            {data: 'created_at', name: 'posts.created_at'},
            {data: 'updated_at', name: 'posts.updated_at'}
        ]
    });

[omar-faruk-sust]

sorry to bother you. i have solved my problem :+1:

[webwizard02]

Good Day!

I am having a problem with the searching of records in my datatables. Only the tracking_id column can be searched. I need all of the columns to be searched.

Here is my query php code:

$result = Document::join('user','document.user_id','=','user.id')
->join('classification','document.classification_id','=','classification.id')
->select('document.id',DB::raw('CONCAT(document.tracking_type,"-",document.tracking_date,"-",document.tracking_seq_no) AS tracking_id'),'document.date_prepared','document.subject','classification.description as doc_class')
->orderBy('document.created_at','desc')->get();

return Datatables::collection($result)
->addColumn('check',function($record){
    return '<input type="checkbox" name="check['.$record->id.']" class="checkboxes" value='.$record->id.'>';})
->addColumn('action',function($record){
    return '<a href="/records/view/' . $record->tracking_id . '">View Record</a></a>';})
->addColumn('document.tracking_id',function($record){
    return $record->tracking_id;})
->editColumn('document.doc_class',function($record){
    return $record->doc_class;})
->addColumn('document.subject',function($record){
    return $record->subject;})
->addColumn('document.date_prepared',function($record){
    return date('F d, Y', strtotime($record->date_prepared));})
->make(true);

JS Declaration:

docstable = $('.docstable').DataTable({
    processing: true,
    serverSide: true,
    ajax: "/ajax/docrec"
    columnDefs: [
        {targets:[0,1], orderable:false}
    ],
    columns: [
        {data:'check'},
        {data:'action'},
        {data:'tracking_id',name:'document.tracking_id'},
        {data:'doc_class',name:'document.doc_class'},
        {data:'subject',name:'document.subject'},
        {data:'date_prepared',name:'document.date_prepared'}
    ]
});

Is there a way like Bllim Datatables that can search all specified columns by using searchColumns() in php code? Thank you