Laravel nested or with and in where condition-CodePudding

My Expected query is

select count(*) as aggregate from `books` 
where (`books`.`is_deleted` = 0) 
and `category_id` = '61' 
and (`title` like '%問いかけの作法 チームの魅力と才能を引き出す技術%' or `title` like '%問イカケノ作法 チームノ魅力ト才能ヲ引キ出ス技術%' or `title` like '%問いかけの作法 ちーむの魅力と才能を引き出す技術%');

I have written my conditions like below ways

        $queryCondition = $this::where(['books.is_deleted' => false]);

        if( isset($queryString['category']) ){
            $queryCondition->where('category_id',$queryString['category']);
        }

        if( isset($queryString['searchKey']) ){
            $search = mb_convert_kana($queryString['searchKey'],"rns");
            $kana = mb_convert_kana($search,"KVC");
            $katakana = mb_convert_kana($search,"KVc");

            $queryCondition->where('title','like','%'.$search.'%')
                ->orWhere('title','like','%'.$kana.'%')
                ->orWhere('title','like','%'.$katakana.'%')
            ;

        }

I'm getting the output query like below

select count(*) as aggregate from `books` 
    where (`books`.`is_deleted` = 0) 
    and `category_id` = '61' 
    and `title` like '%問いかけの作法 チームの魅力と才能を引き出す技術%' or `title` like '%問イカケノ作法 チームノ魅力ト才能ヲ引キ出ス技術%' or `title` like '%問いかけの作法 ちーむの魅力と才能を引き出す技術%';

Without the () in last condition. How can I fix it ? Without this way has there any other ways to implement nested or in laravel ? Example

$query->where([
                'OR' => [
                    [name LIKE' => '%'.$search.'%'],
                    [search LIKE' => '%'.$kana.'%'],
                    [search LIKE' => '%'.$katakana.'%']
            ]
]);

CodePudding user response：

try this

->where(function ($query) {
    $query->where('title','like','%問いかけの作法 チームの魅力と才能を引き出す技術%')
        ->orWhere('title','like','%問イカケノ作法 チームノ魅力ト才能ヲ引キ出ス技術%')
        ->orWhere('title','like','%問いかけの作法 ちーむの魅力と才能を引き出す技術%');
})
->get();

The closure will give you the () that you are seeking for.

Laravel documentation 9.x itself asks to add orwhere via above.

You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.

From docs:

$users = DB::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

which will give you,

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

CodePudding user response：

You should try whereRaw with raw query instead of orWhere

So, instead of this

->orWhere('title','like','%'.$kana.'%')
->orWhere('title','like','%'.$katakana.'%');

Do this

->whereRaw('title LIKE "%$kana%" OR title LIKE "%$katakana%" ');

From my experience, anytime i use orWhere(), it ignores all other conditions so i prefer to use whereRaw and it works fine for me without any issues