same column name in DB for relationship and model causes Datatables to return results from only one DB column

[madsem]

When I'm doing:

$users = User::with('roles')->select('users.*');

and return the datatable like this:

return Datatables::of($users)
                             ->editColumn('name', function (User $user) {
                                 if (Auth::user()->can('edit_users')) {

                                     if ($user->hasRole('superadmin') && ! Auth::user()->hasRole('superadmin')) {
                                         return $user->name;
                                     }
                                     else {
                                         return "<a href='" . route('users.edit',
                                                 $user->id) . "'>{$user->name}</a>";
                                     }
                                 }

                                 return $user->name;
                             })
                             ->editColumn('roles', function (User $user) {
                                 return $user->roles->pluck('name')->implode(',');
                             })
                             ->addColumn('action', function ($id) {
                                 return view('partials.datatables.checkbox', $id);
                             })
                             ->rawColumns(['action'])
                             ->blacklist(['action'])
                             ->make(true);

These are the JS column names and data names for datatables config: (using an array in my view)

'name' => [
                            'name' => 'users.name'
                        ],
                        'email' => [
                            'name' => 'users.email'
                        ],
                        'roles' => [
                            'name' => 'roles.name',
                            'orderable' => 'false',
                        ],
                        'created_at' => [
                            'name' => 'users.created_at'
                        ],
                        'updated_at' => [
                            'name' => 'users.updated_at'
                        ],

This query is run:

select users.id, users.name, users.email, users.created_at, users.updated_at, `roles`.`name` from `users` left join `model_has_roles` on `model_has_roles`.`model_id` = `users`.`id` left join `roles` on `model_has_roles`.`role_id` = `roles`.`id` where LOWER(`roles`.`name`) LIKE '%%adm%%' order by `users`.`created_at` desc limit 25 offset 0

Which gives me the correct results when running directly in sequel pro, but the resulting datatable (when doing a column search for role name) shows the user name and role name the same...

So a user name like "Mr. Foo Bar" shows as "admin" in the datatable, but query returns the correct result.

When I change my query to

$users = User::with('roles')->selectRaw('users.id, users.name AS user_name, users.email, users.created_at, users.updated_at');

and adjust the JS column names to users.user_name it works and columns in datatable shows the correct results when doing a search on the roles column.

System details

• Mac OSX
• PHP Version 7.1
• Laravel Version 5.4
• Laravel-Datatables Version 7.8.1

[kossuth1]

Hi, This problem still exists. Beside that when I specify columns I want to use inside with(), ordering won't use that, it creates the query as follows: select * from...

eg: Lead::with([ "responsible:id,name_full", "status:id,name", "client:id,name" ])

[yajra]

You need to include a select when using relationships.

Lead::with([ "responsible:id,name_full", "status:id,name", "client:id,name" ])
    ->select('leads.*')
    ...

[saskysamonte]

You need to include a select when using relationships.

Lead::with([ "responsible:id,name_full", "status:id,name", "client:id,name" ])
    ->select('leads.*')
    ...

This is working

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.