Laravel-datatables: Search wont work in join query

[willekaputra]

Summary of problem or feature request

Hi, i've got some error when trying to search my table, it says that
Ambiguous column: 7 ERROR: column reference "id" is ambiguous

Here's my code example:

Code snippet of problem

Controller

 public function getTeam() {
        $teams = DB::table('master_users')
        ->join('master_roles','master_roles.id','=','master_users.master_role_id')
        ->where('master_users.head_user','=',Auth::user()->id)
        ->groupBy('master_users.id')
        ->groupBy('master_roles.role')
        ->orderBy('master_roles.role','asc')
        ->distinct()
        ->select(array(
            'master_users.id', 
            'master_users.name', 
            'master_roles.role'
            ));

        return Datatables::of($teams)
        ->addColumn('project_count', function($team) {
            return DB::table('project_member_details')
            ->where('member_id','=',$team->id)
            ->count();
        })
        ->addColumn('project_finished', function($team) {
            return DB::table('project_member_details')
            ->where('member_id','=',$team->id)
            ->where('is_finished','=','y')
            ->count();
        })
        ->addColumn('group', function($team) {
            return Auth::user()->groups->first()->group_name;
        })
        ->addColumn('view', function($team) {
            return '<a type="button" id="btnView" class="btn btn-primary btn-xs" href="team/view/'.$team->id.'">
                View
            </a>
            ';
        })
        ->removeColumn('id')
        ->make();
    }

View

 $(document).ready(function(){
        $('#teamTable').DataTable({
            processing: true,
            serverSide: true,
            ajax: '{{ url("/manager/team/getTeam") }}',
            columns: [
                {data: 'name', name: 'master_users.name'},
                {data: 'role', name: 'master_roles.role'},
                {data: 'project_count', orderable: false, searchable: false},
                {data: 'project_finished', orderable: false, searchable: false},
                {data: 'group', orderable: false, searchable: false},
                {data: 'view', orderable: false, searchable: false}
            ]
        });
    });

System details

• Operating System
• PHP Version
• Laravel Version
• Laravel-Datatables Version

[yajra]

Please see this demo for ref. Also double check that you are not referencing the id column directly. You need to use table.id convention to avoid this issue. Thanks!

[yajra]

Using table_name.id as name of your column in your js should fix the issue. Thanks!

[mayadipto]

Using table_name.id as name of your column in your js should fix the issue. Thanks!

It worked

[vahidalvandi]

how i can solve duplicate column name db??

[meeprakash25]

I didn't get:
_- Using table_name.id as name of your column in your js should fix the issue._