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

Laravel nested or with and in where condition

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.'%']
            ]
]);
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search