Блог

У меня проблема с поиском в столбце JSON. Laravel 8.x и MySQL 5.7.x

Учитывая следующий столбец 'industry_focus' , я пытаюсь сопоставить и вернуть все записи, содержащие любое из значений

["1","2","3","4","5","6","7","8"]

Мой запрос выглядит так:

 $related = Broker::query()
->where('broker_name', '!=', null)
->where('is_active', '=', true)
->where('state', '=', $state->code);

foreach ($same_industries as $key => $value) {
    if ($key === 0) {
        $related->whereJsonContains('industry_focus', (string) $value->id);
    } else {
        $related->orWhereJsonContains('industry_focus', (string) $value->id);
    }
}

$related = $related
->limit(10)
->get();
 

И он выдает необработанный запрос, выглядящий следующим образом:

 SELECT *
FROM `brokers`
WHERE `broker_name` IS NOT NULL
    AND `is_active` = 1
    AND `state` = 'NY'
    AND json_contains(`industry_focus`, '"2"')
    OR json_contains(`industry_focus`, '"3"')
    OR json_contains(`industry_focus`, '"4"')
    OR json_contains(`industry_focus`, '"5"')
    OR json_contains(`industry_focus`, '"6"')
    OR json_contains(`industry_focus`, '"7"')
    OR json_contains(`industry_focus`, '"8"')
    OR json_contains(`industry_focus`, '"9"')
    OR json_contains(`industry_focus`, '"10"')
    OR json_contains(`industry_focus`, '"11"')
    OR json_contains(`industry_focus`, '"12"')
    OR json_contains(`industry_focus`, '"13"')
    OR json_contains(`industry_focus`, '"14"')
    OR json_contains(`industry_focus`, '"15"')
    OR json_contains(`industry_focus`, '"16"')
limit 10
 

К сожалению, это неверно и возвращает все записи, игнорируя все условия. Я думаю, что перед первой AND json_contains и закрывающей скобкой отсутствует скобка в конце? Это ошибка, или я что-то упустил. Есть ли лучший способ написать это?

РЕДАКТИРОВАТЬ: Хорошо, итак, мне удалось как-то исправить это самостоятельно, посмотрев примеры в другом месте. Это то, что работает для меня:

 $related = Broker::query()
    ->where('broker_name', '!=', null)
    ->where('is_active', '=', true)
    ->where('state', '=', $state->code);

$related->where(function ($query) use ($same_industries) {
    foreach ($same_industries as $key => $value) {
        if ($key === 0) {
            $query->whereJsonContains('industry_focus', (string) $value->id);
        } else {
            $query->orWhereJsonContains('industry_focus', (string) $value->id);
        }
    }
});

$related = $related
    ->limit(10)
    ->get();

 

Прежде всего, вам нужно просмотреть булеву алгебру. В этом случае существует приоритет оператора между AND и OR , который вас раздражает.

 A AND B OR C OR D
 

Эквивалентно

 (A AND B) OR C OR D
 

Но то, что вам нужно, это:

 A AND (B OR C OR D)
 

Чтобы получить это в логическом выражении, вам нужно явно использовать круглые скобки, чтобы переопределить естественный приоритет оператора.

Что еще могло бы упростить этот запрос, это то, что я говорю многим людям, которые используют JSON в MySQL:

Не используйте JSON в MySQL, если вы хотите использовать элементы внутри документа JSON в качестве отдельных элементов данных и искать их с помощью выражений SQL.

В этом случае у вас есть многозначный атрибут industry_focus . Обычный способ реализации многозначного атрибута — создать вторую таблицу и хранить по одному значению в строке.

 CREATE TABLE broker_industry_focus (
  broker_id INT NOT NULL,
  industry_focus INT NOT NULL,
  PRIMARY KEY (broker_id, industry_focus)
);
INSERT INTO broker_industry_focus VALUES (123, 7), (123, 11);
 

Как только вы это сделаете, вам будет проще искать:

 SELECT *
FROM brokers AS b
JOIN broker_industry_focus AS f 
    ON b.id = f.broker_id
WHERE b.broker_name IS NOT NULL
    AND b.is_active = 1
    AND b.state = 'NY'
    AND f.industry_focus IN (1,2,3,4,5,6,7,8);
 

Вам также может понравиться моя презентация о том, как неправильно использовать JSON в MySQL.