Приклад використання змінних MySQL для підвищення швидкодії Laravel-проекту

Кращий спосіб оптимізувати SQL-запит — це зробити його непотрібним, зберігаючи функціональність.

Зараз ми побачимо, як можна використовувати для цього змінні MySQL.

Опис завдання

Є маркетплейс. Є таблиці «suppliers» та «products», пов’язані багато-до-багатьох зі зведеною таблицею «warehouse».

Постачальники продукції можуть експортувати її на маркетплейс за допомогою файлу Excel.

Маркетолог ставить завдання: створити звіт для кожного експорту:

• Скільки товарів у таблиці «warehouse» було додано, і на яку кількість товарів,
• Скільки товарів було змінено і наскільки змінилася вартість товарів зі змінами.

Ці звіти слід зберігати в таблиці «import_reports».

У моєму прикладі я покажу, як ми можемо уникнути додаткового SQL-запиту під час збереження цього звіту. Для цього ми використовуватимемо змінні MySQL.

Опис логіки рішення

Вся логіка винесена до класу ImportAction, у його метод handle(). Розберемо його повністю. Я сподіваюся, цього буде достатньо для розуміння. Але якщо раптом недостатньо — Ви можете подивитися в ГітХабі приклад, що повністю працює (посилання в кінці статті).

Повний текст цього методу:

    /**
     * Import quantities and prices from an Excel file.
     * @param ImportRequest $request
     * @return RedirectResponse
     * @throws IOException
     * @throws UnsupportedTypeException
     * @throws ReaderNotOpenedException
     */
    public function handle(ImportRequest $request): RedirectResponse
    {
        $rowsCollection = (new FastExcel())->import($request->validated('file'));
        $rows = $rowsCollection->toArray();

        if (empty($rows)) {
            return back()->with('error', 'No data found in the file.');
        }

        DB::statement('SET @quantity_inserted = 0, @quantity_updated = 0,
                                 @amount_inserted = 0.0, @amount_updated = 0.0');

        Warehouse::query()->upsert(
            $rows,
            ['name'],
            ['quantity','price']
        );

        $sql = "insert into import_reports
              (supplier_id, quantity_inserted, quantity_updated, amount_inserted, amount_updated,
               created_at, updated_at)
              values
              (?, @quantity_inserted, @quantity_updated, @amount_inserted, @amount_updated,
               now(), now())
              ";

        DB::insert($sql, [$rows[0]['supplier_id']]);

        ImportCompleted::dispatch(DB::getPdo()->lastInsertId());

        return back()->with('success', 'File imported successfully.');
    }

Розберемо ключові моменти

Для імпорту даних із файлу Excel використовуємо пакет FastExcel. В результаті її роботи маємо масив $rows, що містить рядки Excel-файла.

Необхідні тригери

Щоб наше рішення спрацювало, на таблицю «warehouse» ми повісимо два тригери:

CREATE TRIGGER warehouse_after_insert AFTER INSERT ON `warehouse`
FOR EACH ROW
BEGIN

    if @quantity_inserted is null then
        set @quantity_inserted = 0;
    end if;

    if @amount_inserted is null then
        set @amount_inserted = 0.0;
    end if;

    set @quantity_inserted = @quantity_inserted + 1;
    set @amount_inserted = @amount_inserted + NEW.quantityNEW.price;

END

/ ----------------------- */

CREATE TRIGGER warehouse_after_update AFTER UPDATE ON `warehouse`

FOR EACH ROW
BEGIN
    DECLARE delta DECIMAL(15,2);
    if @quantity_updated is null then
        set @quantity_updated = 0;
    end if;

    if @amount_updated is null then
        set @amount_updated = 0.0;
    end if;

    SET delta = NEW.quantity*NEW.price - OLD.quantity*OLD.price;

    set @amount_updated = @amount_updated + NEW.quantity*NEW.price - OLD.quantity*OLD.price;
    if delta <> 0.0 then
        set @quantity_updated = @quantity_updated + 1;
    end if;

END

Змінні MySQL ініціалізуються перед імпортом з файлу Excel:

DB::statement('SET @quantity_inserted = 0, @quantity_updated = 0,
                         @amount_inserted = 0.0, @amount_updated = 0.0');

Потім ми виконуємо метод upsert для моделі Warehouse, і ось тут і відбувається найцікавіше.

При кожній зміні або додаванні запису спрацьовує тригер, який редагує наші змінні.

І після того, як upsert() закінчить роботу, у нас будуть усі необхідні значення в змінних MySQL — нам не потрібні SQL-запити, що отримають кількість і суму змін. Залишилося тільки записати їх у звіт:

$sql = "insert into import_reports
      (supplier_id, quantity_inserted, quantity_updated, amount_inserted, amount_updated,
       created_at, updated_at)
      values
      (?, @quantity_inserted, @quantity_updated, @amount_inserted, @amount_updated,
       now(), now())
      ";
DB::insert($sql, [$rows[0]['supplier_id']]);

Ось і все! Кинемо подію ImportCompleted для майбутніх нотифікацій чи чогось додаткового — і імпорт готовий!

Повний код цього пет-проєкту Ви можете подивитися на Гітхабі. З освітньою метою деякі речі були спрощені, щоб не відволікали від основної ідеї.

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті