Блог

Главная - Вопросы по программированию - Laravel объединяет таблицы с помощью поля JSON, когда некоторые поля не имеют внешнего ключа

Laravel объединяет таблицы с помощью поля JSON, когда некоторые поля не имеют внешнего ключа

#php #mysql #laravel

#php #mysql #laravel

Вопрос:

Я хочу объединить 2 таблицы с помощью внешнего ключа, хранящегося в поле JSON. Поле может быть пустым, поэтому я получаю сообщение об ошибке, от которого я не могу избавиться.

У меня есть модель Transaction :

 class Transaction extends BaseModel
{
    protected $casts = [
        'items' => 'json',
    ];

items поле json:

 [{"event_id":25,"article_nr":"000123","title":"My Event"}]

теперь я хочу объединить эти таблицы для запроса:

 Transaction::join('events','transactions.items->event_id', '=', 'events.id')
->get();

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

SQL-запрос от Eloquent:

 select * from `transactions` inner join `events` on json_unquote(json_extract(`transactions`.`items`, '$."event_id"')) = `events`.`id`

Поэтому для строк с пустым items полем я получаю сообщение об ошибке:

 3141 Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.

Есть ли способ изменить мое соединение и выбрать только строки, содержащие items->event_id ключ?

[редактировать] добавлен необработанный запрос SQL

Комментарии:

1. или даже может быть пустым , каково значение в данном случае.

2. пустая строка

3. Замените допустимым JSON. Например, пустой массив.

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

5. что ж, следующая проблема: мои объекты-объекты представляют собой массив, если вы посмотрите на скобки в данных. eloquent переводит его в inner join events on json_unquote(json_extract(transactions.items, '$."[0]event_id"')) = events.id , но мне нужно inner join events on json_unquote(json_extract(transactions.items, '$[0]."event_id"')) = events.id

Ответ №1:

У меня это работает…

После помощи от Akina (спасибо), которая упомянула, что все поля должны быть заполнены как минимум пустым объектом json:

 Update transactions set items = '{}' where items = '' or items is null;

Я смог объединить таблицы с:

 Transaction::select(['transactions.*'])
                ->leftJoin('events',DB::raw("json_extract(transactions.items, '$[0].event_id')"),"=","events.id")

Эта '$[0].event_id' часть является особенной для этого объединения, потому что у меня есть мой event_id в объекте, и этот объект находится в массиве:

 [{"event_id":24}]

(Обратите внимание на внешние скобки)

Без внешнего массива это было бы легко, и я мог бы просто сделать это без необработанного sql, например:

 Transaction::leftJoin('events','transactions.items->event_id', '=', 'events.id')

[ПРАВИТЬ]
ОКОНЧАТЕЛЬНОЕ РЕШЕНИЕ

Вы столкнетесь с проблемами производительности при объединении с помощью полей JSON. Мой запрос занял около 160 секунд.

Конечным решением было создать сгенерированный столбец event_id из items строки. «Сгенерированный столбец» MySQL автоматически заполняется из его выражения и автоматически обновляется MySQL / MariaDB при обновлении поля items.

 ALTER TABLE transactions ADD event_id int(10) UNSIGNED AS (JSON_UNQUOTE(items->"$[0].event_id")) STORED;

Миграция laravel для этого будет

 $table->bigInteger('event_id')->unsigned()->storedAs('JSON_UNQUOTE(items->"$[0].event_id")');

Теперь мой запрос занимает всего 0,2 секунды вместо 160 секунд.