Search through Many to Many Relationship in Laravel

Question

I am building a Laravel API. I have two models, role and permissions which has many to many relationship between them. A role can have many permissions and a permission can belong to many roles. I have a pivot permissions_roles table.

On the front end, I have a date and status filter and also a search functionality. I want to be able to type a search keyword and be able to search for it on the roles and permissions table. i.e if I type create, it should match roles with name "create" or bring out the results for roles that have "create" in one of their permissions.

This is what I have, but I am not getting the desired results especially when I combine request parameters.

$roles = Role::with('permissions','users');

if($request->search_term){
    $roles->whereHas('permissions', function ($query) use ($request) {
        $query->where('name', 'like', '%' . $request->search_term . '%');
    })->orWhere('name', 'like', '%' . $request->search_term . '%');
}

$roles->when($request->status !== null, function (Builder $query) use ($request) {
    $statuses = isIntegerOrCommaSeparatedInteger($request->status, 'statuses');
    return $query->whereIn('active', $statuses);
});

$roles->when($request->start_date && $request->end_date, function (Builder $query) use ($request) {
    return $query->whereBetween(
        DB::raw('date(created_at)'),
        [
            Carbon::createFromDate($request->start_date)->toDateString(),
            Carbon::createFromDate($request->end_date)->toDateString()
        ]
    );
});

$roles = $roles->get();

Is there a better way in which this can be achieved?

Thanks

Answer 1

Thanks Mahdi Rashidi. That worked. So, in case anyone comes across this. I replaced the search query with what Mahdi suggested.

$roles = Role::with('permissions', 'users');

            if ($request->search_term) {
                $roles->where(function ($q) use ($request) {
                    return $q->whereHas('permissions', function ($query) use ($request) {
                        $query->where('name', 'like', '%' . $request->search_term . '%');
                    })
                        ->orWhere('name', 'like', '%' . $request->search_term . '%');
                });
            }


            $roles->when($request->status !== null, function (Builder $query) use ($request) {
                $statuses = isIntegerOrCommaSeparatedInteger($request->status, 'statuses');
                return $query->whereIn('active', $statuses);
            });

            $roles->when($request->start_date && $request->end_date, function (Builder $query) use ($request) {
                return $query->whereBetween(
                    DB::raw('date(created_at)'),
                    [
                        Carbon::createFromDate($request->start_date)->toDateString(),
                        Carbon::createFromDate($request->end_date)->toDateString()
                    ]
                );
            });

            $roles = $roles->get();

Thanks