Question 15336 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to filter by composite criteria in Laravel?

So i have codes that can search data based on relation columns,
there are 2 search input, 1 of them work really well but the other one doesn’t work.

my Model

Category

  • id
  • name

Task

  • id
  • title
  • category_id
  • company_id

Company

  • id
  • name

this is my blade:

<form action="/admin/manage" method="get">

     <!--1. this is search name -->
    <input name="search_name">

     <!--2. this is company_id search -->
    <select name="company_id" >
             <option value="">Select All</option>
              @foreach($companies as $company)
                <option value="{{ $company->id }}" >{{ $company->name }}</option>
              @endforeach
    </select>
</form>

my controller:

if($request->search_name || $request->company_id){
            $categories = Category::with(['tasks' => fn($query) => $query->where('title', 'like', '%' . $request->search_name . '%')])
                        ->whereHas('tasks', fn ($query) => 
                        $query->where('title', 'like', '%' . $request->search_name . '%')
                        )->get();
}
else{
    $categories = Category::with('tasks')->get();
}

this code seems unfinished, it is only search for title in my task and i don’t know how to add more code that can search 2 columns together

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    This is my own code, i added array inside where() parameter it works too,

    $categories = Category::with(['tasks' => fn($query) => $query
                        ->where([
                            ['title', 'like', '%' . $request->search_name . '%'],
                            ['company_id', 'like', '%'. $request->company_id .'%'],
                        ]     
                        )])
                        ->whereHas('tasks', fn ($query) => 
                        $query->where([
                            ['title', 'like', '%' . $request->search_name . '%'],
                            ['company_id', 'like', '%'. $request->company_id .'%'],
                        ]))->get();

  2. 0

    You can try this to search from both of your values:

    if($request->search_name || $request->company_id){
    $searchName = $request->search_name;
    $companyId = $request->has('company_id') ? $request->company_id : false;

    $categories = Category::with(['tasks'])
                    ->whereHas('tasks', function($query) use ($searchName, $companyId) {
                        $query->where('title', 'like', '%' . $searchName . '%');
                        if($companyId) {
                            $query->orWhere('company_id', $companyId);
                        }
                    })->get();
} else{
    $categories = Category::with('tasks')->get();
}
    Login or Signup to reply.
  3. 0

    You can chain where so the query that is being generated will end up having a LIKE AND an = criteria:

    if($request->search_name || $request->company_id){
            $categories = Category::with(['tasks' => fn($query) => $query->where('title', 'like', '%' . $request->search_name . '%')])
                        ->whereHas('tasks', fn ($query) => 
                        //search_name might be empty, in which case we do not want to compute a costly wildcard comparison that all records will fulfill as long as they are not null
                        if ($request->search_name) {
                            $query->where('title', 'like', '%' . $request->search_name . '%')
                        }
                        //We check whether we need a criteria for company_id
                        if ($request->company_id) {
                            $query->where('company_id', $companyId);
                        }
                        )->get();
}
else{
    $categories = Category::with('tasks')->get();
}
    Login or Signup to reply.
  4. 0

    Because you use Laravel 8, you can not use withWhereHas(). So you can define a function in your class and reuse it:

    function filterTasks($query, $request)
{
    if ($request->search_name) {
        $query->where('title', 'like', '%' . $request->search_name . '%');
    }

    if ($request->company_id) {
        $query->where('company_id', $request->company_id);
    }
}

    An then:

    $categories = Category::whereHas('tasks', function ($query) use ($request) {
    $this->filterTasks($query, $request);
})->with([
    'tasks' =>function ($query) use ($request) {
        $this->filterTasks($query, $request);
    }
])->get();

    If you use Laravel 9 or above, you should use withWhereHas(). The method applies eager loading and check the condition at the same time. It makes your code shorter, cleaner and you don’t have duplicated codes.

    $categories = Category::withWhereHas('tasks', function ($query) use ($request) {
    if ($request->search_name) {
        $query->where('title', 'like', '%' . $request->search_name . '%');
    }

    if ($request->company_id) {
        $query->where('company_id', $request->company_id);
    }
})->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search