Search code examples
laraveleloquentlaravel-11

How to replicate SQL with sub grouped OR expressions in laravel


I am looking to implement a search function, that optionally includes the various fields that I have for my users to search on.

My code thusfar:

        $categories = $request->input('category'); // array(int)
        $textSearch = $request->input('text'); // string

        $query = Product::query();

        if(!is_null($categories)){
            //$query->selectSub();
            foreach($categories as $catId){
                $category = ShopCategory::find($catId);

                $query = $query->whereBelongsTo($category);//TODO
            }
        }
        if(!is_null($textSearch)){
            $query = $query->whereAny([
                'name',
                'description',
                'shortDescription',
            ],
                'like', '%'.$textSearch.'%');
        }

        $searchResults = $query->paginate(36);

My goal is to reach an SQL statement equivalent to:

SELECT * FROM products WHERE (shop_category_id = ? OR shop_category_id = ?) AND (/* text search LIKE */)

But thusfar, I end up with more of a :

SELECT * FROM products WHERE shop_category_id = ? AND shop_category_id = ? AND (/* text search LIKE */)

Where each of the category id's are ANDed as part of the overall query instead of ORed in a group.

I am aware of or*() query methods, but it is unclear how to properly group them for the desired effect instead of just ORing them along with the text search.

Edit: Based on the accepted answer, I landed on this:

            $mainQuery->where(function($query) use ($categories) {
                foreach ($categories as $catId) {
                    $category = ShopCategory::find($catId);
                    $query->orWhereBelongsTo($category);
                }
            });

Solution

  • You can wrap some where condition into a group by using the where function with a callback. Any condition applied on the builder, inside the callback, will be wrap in parathesis. For example:

    Model::query()->where('condition1', 'value')->where(function($query) {
      // those where will be wrapped into ()
      $query->where('condition2', 'value')->orWhere('condition3', 'value');
    })
    

    Will give the following query:

    SELECT *
    FROM models
    WHERE conditon1 = 'value'
      AND (condition2 = 'value'
           OR condition3 = 'value')
    

    And since it doesn't matter if the first where is a OR or AND, you can put that into a loop without worring about the first OR:

    $columns = [
     'column1', 
     'column2',
     'column3'
    ]
    
    Model::query()->where('condition1', 'value')->query(function($query) use ($columns) {
      foreach($columns as $column) {
        $query->orWhere($column, 'value');
      } 
    });
    

    This query is dynamically generated and will return the follow SQL:

    SELECT *
    FROM models
    WHERE condition1 = 'value'
      AND (column1 = 'value'
           OR column2 = 'value'
           OR column3 = 'value')
    

    And since the "column conditions" are wrapped into a group, they won't interfere with the first condition.