Laravel Eloquent或查询构建器:在AND条件中使用or条件

Question

数据流：

4-5个主类别，每个主类别有10-15个子类别。属于少数类别的少数服务。类别作为序列化数据存储在服务表中，如下所示：

a:2:{i:0;s:1:"1";i:1;s:2:"3";}
a:1:{i:0;s:3:"2";}
a:1:{i:0;s:3:"3";}
a:2:{i:0;s:1:"1";i:1;s:3:"3";}

发布的表单数据如下：

array(
    [0] => array(
        [0] => 1,
        [1] => 3,
    ),
    [1] => array(
        [0] => 2
    ),
    ....
    ....
    ....
)

在同一主类别中的每个所选类别被分组到array.Also中的情况下，类别和子类别的数量是不固定的

用于获取记录的纯SQL

SELECT * FROM `services` WHERE (`categories` LIKE '%"1"%' OR `categories` LIKE '%"3"%') AND (`categories` LIKE '%"2"%') AND ( ... OR ...) AND ( ... OR ...) ....

我已经绑定了这个，但是这个似乎不起作用。

\DB::table ('services')
->where(function($q) use ($categories) {
    foreach($categories as $category) {
        $q->where('services.categories', 'like', '%"'.DB::raw($category).'"%');
    }
})

Answer 1

使用查询构建器在Laravel中进行查询

$queryBuilder = \DB::table('services');

//loop to create dynamic where query
foreach($categories as $category){

        //create a query as (condition OR condition)
        $queryBuilder->where(function($query) use($category) {

            $query->where('categories', 'LIKE', "%" . $category[0] . "%");

            //if count is two then adding the or where clause 
            if(count($category) === 2){
                  $query->orWhere('categories', 'LIKE', "%" . $category[1] . "%");
            }

        });
    }
}

$result = $queryBuilder->get(); //executing the query to get result