How to make laravel query with search condition and filter condition from relational table-CodePudding

I have models 'Case' and 'Type' in many to many relation. 'Case' belongs to a 'Type'. Now I am trying to implement search and filter in my case index. I have already included the search in the query builder so that the keywords match multiple columns and also columns in related table. Now I am trying to incorportae a filter also. But when I am including that filter in query builder, it is not working. Including either search or filter is working but including both together is not.

Here is my code to include the search in query builder:

    $key = $this->search;
    $cases = Landcase::query()->orderBy('created_at', 'DESC');

    $cases
    ->where('number', 'like', '%'.$this->search.'%')
    ->orWhere('title', 'like', '%'.$this->search.'%')
    ->orWhereHas('type', function ($query) use ($key) {
        $query->where('name', 'like', $key.'%');
    });

This much is perfectly working. I can search in title, number and type's name. Now here is the additional code to include the filter with type id(s) so that only specific type's cases will be shown and the search will work on those only.

  if($this->selected_types){
        $ids= $this->selected_types;
        $cases->whereHas('type', function ($query) use ($ids){
            $query->whereIn('id', $ids);
        });
    }

this block is not affecting the collection. But if I comment out the first block, this works. How can I incorporate it to my existing query builder block?

CodePudding user response：

You have several OR in your where clauses so adding the last part makes it something like:

WHERE … OR … OR (… AND …)

What you need is to group your orWheres:

$cases->where(function ($query) {
  $query
    ->where('number', 'like', '%'.$this->search.'%')
    ->orWhere('title', 'like', '%'.$this->search.'%')
    ->orWhereHas('type', function ($query) use ($key) {
        $query->where('name', 'like', $key.'%');
    });
});

More here: https://laravel.com/docs/9.x/queries#or-where-clauses