How to use "OR LIKE" within "AND" condition Laravel

Question

I'm new to laravel and I'm facing some problems in fetching records. The table structures are as follows :

main_categories
------------------
id      name
1       NNN
2       PPP

categories
-----------------------------------
id      name    main_category_id
-----------------------------------
1       abc         1
2       xyz         2
3       lmn         1

services
------------------
id  categories
------------------
1   a:2:{i:0;s:1:"1";i:1;s:2:"3";}
2   a:1:{i:0;s:3:"2";}
3   a:1:{i:0;s:3:"3";}
4   a:2:{i:0;s:1:"1";i:1;s:3:"3";}

Categories stored in the services are in serialized form.

In a form I have dropdowns according to the main category and in each dropdown there are sub categories.

The post data are in this format :

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

The process id like this : 5 dropdowns as per main category and the options are their sub categories and I want to put 'OR' condition in between the sub categories or same main category and 'AND' condition with the other set of 'OR' conditions of other Main Category.

In raw SQL I do this query :

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

In Laravel I tried the following :

$categories = [1,3];

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

But this is not working. How this could be properly done in Laravel.

Answer 1

The code is a bit ugly, but probably will work for your case. So, try this:

$categories = [1,3];

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