CODEWITHSUNDEEP
mysqllaravellaravel-5query-builder
myol
myol

Reputation: 9838

Filter out multiple where not like

I am trying to RTM but cannot see where the list of possible where operators are listed. I only see the basic examples of >=, <> and like. Anyways, I want to filter out any results that match multiple patterns.

If I use 

->whereNotIn('fieldName', ['exactString1', 'exactString2'])

Then I filter these exact strings from my results. However I want to filter out multiple patterns (NOT LIKE) rather than multiple strings.

The patterns are generated dynamically so using an array would be preferable. So something like

->whereNotLike('fieldName', ['%string1%', '%string2%'])

I am using MySQL so I'm open to using a raw query is the query builder does not support this.

So if I have results of

string1_abc
string2_abc
string3_abc

then I would get

string3_abc

returned.

I cannot use a whitelist and the blacklist patterns are generated dynamically.

Upvotes: 1

Views: 140

Answers (2)

Joel Hinz
Joel Hinz

Reputation: 25404

Here's a very basic example of how you can add multiple conditions on the fly to an existing query:

$patterns = [
    'field1' => ['pattern1', 'pattern2', 'pattern3'],
    'field2' => ['pattern4', 'pattern5'],
];

$query = MyModel::where('x', 'y'); // just an example of a query builder instance

foreach ($patterns as $field => $pattern) {
    $query->where($field, 'not like', '%'.$pattern.'%');
}

return $query->get();

It obviously won't look exactly like this in your application, but hopefully you get the idea. :)

Note that you don't have to do $query = $query->where(...); since you're transforming the existing query rather than creating a new one.

Upvotes: 2

Alexey Mezenin
Alexey Mezenin

Reputation: 163898

You can use 'not like', like:

->where('fieldName', 'not like', '%string%')

Upvotes: 1

Related Questions