How to handle N+1 problem?

[edpmasterindo]

I Have this code in my controller to create user table

`if($request->ajax()){
return DataTables::of(User::orderBy('name')->get())
->addIndexColumn()
->addColumn('role', function(User $user){
return $user->roles()->pluck('name')->first();
})
->addColumn('action', function(User $user){
return '

<a class="btn btn-link btn-sm text-decoration-none text-warning" href="'.route("users.edit", $user).'" data-bs-toggle="tooltip" data-bs-title="Edit User">

';
})
->rawColumns(['action'])
->tojson();
}

    return view('dashboard.users.index');`

And get N+1 problem with 1000 user, it query 1001 times to database. Even i try to eager loading the user model with User::with('roles') but still the problem persist.... any solution?

[Arne1303]

I think the problem with eager loading is that you use $user->roles()->pluck('name') which makes a new request. Try using $user->roles->first()->name when using User::with('roles')

[edpmasterindo]

Thank you so much.....

change the code to this, and problem solved.

if($request->ajax()){
            return DataTables::of(User::with('roles')->orderBy('name')->get())
                ->addIndexColumn()
                ->addColumn('role', function(User $user){
                    return $user->roles->first() ? $user->roles->first()->name : '';
                })
                ->addColumn('action', function(User $user){
                    return '
                        <a class="btn btn-link btn-sm text-decoration-none text-info" href="/dashboard/resetpassword/'.$user->id.'" data-bs-toggle="tooltip" data-bs-title="Reset Password"><i class="bi bi-arrow-repeat"></i></a>
                        <a class="btn btn-link btn-sm text-decoration-none text-warning" href="'.route("users.edit", $user).'" data-bs-toggle="tooltip" data-bs-title="Edit User"><i class="bi bi-pencil-square"></i></a>
                        <form action="'.route('users.destroy', $user).'" method="post" class="d-inline">
                            <input type="hidden" name="_method" value="delete">
                            <input type="hidden" name="_token" value="'.csrf_token().'" />
                            <button type="submit" class="btn btn-link btn-sm text-decoration-none text-danger" data-bs-toggle="tooltip" data-bs-title="Delete User" onclick="return confirm(\'are you sure?\')"><i class="bi bi-trash3  "></i></button>
                        </form>
                    ';
                })
                ->rawColumns(['action'])
                ->tojson();
        }