CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Pranab
Pranab

Reputation: 191

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

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.

Upvotes: 5

Views: 2272

Answers (3)

ManojKiran
ManojKiran

Reputation: 6351

I have recently had same issue while trying to search the data based on given array of value. So By looping orWhere inside the wrapped where has generated the query that I was looking for

$array = [
        [1, 3],
        [2],
        [7,10],
        [5, 9, 13],
        [8],
    ];

    DB::table('services')
        ->where(function ($query) use ($array) {
            foreach ($array as $eachArray) {
                $query->where(function ($query) use ($eachArray) {
                    foreach ($eachArray as $eachElement) {
                        $query->orWhere('categories', 'LIKE', $eachElement);
                    }
                });
            }
        })
        ->dd();

So this is the output of the Above Query Builder.

select * from `services` where ((`categories` LIKE ? or `categories` LIKE ?) and (`categories` LIKE ?) and (`categories` LIKE ? or `categories` LIKE ?) and (`categories` LIKE ? or `categories` LIKE ? or `categories` LIKE ?) and (`categories` LIKE ?))

Upvotes: 0

Laerte
Laerte

Reputation: 7083

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).'"%');
        }
    }
});

Upvotes: 1

Yada
Yada

Reputation: 31265

I believe you are lookig for orWhere():

orWhere('name', 'like', 'John')

Upvotes: 0

Related Questions