MySQL / Eloquent greedy regexp

user_12958734 0
Viewed 207

I am trying to query a MySQL table using Laravel's ORM Eloquent (kinda irrelevant I guess). I have a field in the table called quantitative, which contains a JSON in the format [{name: 'a', value: 3}, {name: 'b', value: 4}].

I want the user to be able to query all the entries that contain values that he specified in whatever order. 

AllowedFilter::callback('quantitative', function (Builder $query, $value) {
    $quantitative = collect($value)->filter(function ($el) {
        return isset($el['value']);
    })->pluck('value')->map(function ($el) {
        return "(?=.*{$el})";
    })->implode('');

    $query->where('quantitative', 'regexp', "({$quantitative}).*");
}),

that generate's query (below) using regex (?=.*a)(?=.*b).*

SELECT count(*) as aggregate from `table` where `quantitative` regexp ((?=.*a)(?=.*b)).* (...)

It produces the following error:

SQLSTATE[42000]: Syntax error or access violation: 1139 Got error 'repetition-operator operand invalid' from regexp

Regex itself is working just fine when I'm validating it at regex101.com, the issue I have is it's not valid for MySQL syntax. Is there any way to make it work or use different regex to accomplish the task?

eloquent laravel mysql regex
1 Answers

Seems like either of these would work:

LIKE '%a%b%'

REGEXP 'a.*b'

Please provide a short list of strings that should match and a short list that should not match.

If you are trying to find things inside a JSON string, I suggest either

  • Don't use JSON
  • Use the JSON functions, if your version of MySQL is new enough.

REGEXP and LIKE are much too kludgy. Note that [{name: 'b', value: 4}] contains both an a and a b.

user_17668310
Related