myol myol - 3 months ago 7
MySQL Question

Filter out multiple where not like

I am trying to RTFM 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.

Answer

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.