Использование Illuminate/Database/Query/Expression для работы с чистым sql в объектах

В прошлую пятницу я решил обновить зависимости на боевом проекте. Через какое-то время я заметил, что система не может сохранить данные в базу данных. Причина выяснилась довольно быстро: в новой версии laravel присутствовала ошибка из-за которой при сохранении в базу данных булевого значения, оно сохранялось как числовое. В процессе поиска места для быстрого патча, я обратил внимание на механизм генерации sql для объектов и увидел кое-что новое для себя.

Sql стал одним из стандартов сегодя, отчасти потому что за небольшим количеством простых инструкций он предлагает огромные возможности для разработчиков. С другой стороны, в процессе упрощения работы с sql разработчики создали ORM. Одним из минусов использования ORM является, то что она оскудняет доступный вам sql, делает более трудоемким использование отдельных конструкций.

Если вы работали с PDO, то вы знаете что такой код будет вызывать ошибку:

<?php 
$pdo = new PDO();
$query = $pdo->prepare('select * from table where id IN ?');
$result = $query->execute(['1,2,3'])

Причина ошибки в том, что PDO перед подстановкой приводит значения к нужному типу и экранирует их. Соответственно проверив колонку id, на вход оно ожидает получить число. Разбираясь со внутренностями laravel, я обратил внимание на класс Illuminate/Database/Query/Expression. Этот класс предоставляет возможность подстановки sql выражений не только в запросы, которые делаются через query builder, но и в сами модели.

Рассмотрим следующую модель:

CREATE TABLE sales(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL AUTO_INCREMENT,
    amount INT NOT NULL,
    date INT NOT NULL,
);

CREATE TABLE report(
    id INT NOT NULL AUTO_INCREMENT,
    amount INT NOT NULL,
    date INT NOT NULL,
);

Мы будем складывать в одну таблицу события продажи, а в другую - агрегированные отчеты. Во первых мы можем сделать все на чистом sql(в примере псевдо sql):

INSERT INTO report(amount, date) VALUES((SELECT SUM(amount) FROM sales WHERE date between CURDATE() AND CURDATE() - INTERVAL 1 DAY),NOW()):

Однако это не самый лучший подход. Фактически вы приходите к дублированию логики: одна часть будет лежать в моделях, другая лежать в чистом sql. Однако даже такой метод вставки не лишин недостатков: он не гарантируют, что между запросом и подзапросом не будет вставлена новыя запись. Для защиты от этого нужно использовать блокировки.

Можно переписать этот же код, но сделать подсчет на моделях или агрегирующих фунциях. При этом не будет дублирования кода, но это, во-первых, создаст лишнюю нагрузку на сервер(так как это будут два запроса: один на подсчет и один на вставку), во-вторых, такой метод увеличивает вероятность показать неверных результаты, если мы будем подсчитывать данные, которые менются в текущий момент(после первого запроса, но перед вторым может быть вставка данных).

Наиболее оптимальным решением будет оборачивание подзапроса в Illuminate/Database/Query/Expression:

<?php
class Report extends Model
{

    protected $table = 'report';
}

$report = new Report();
$report->date = time();
report->amount = Illuminate/Database/Query/Expression('(SELECT SUM(amount) FROM sales WHERE date between CURDATE() AND CURDATE() - INTERVAL 1 DAY )');
$report->save();

При этом мы получаем sql близкий к нативному, но полученный через объекты. При этом этот код значительно более оптимален, чем при работе только с объектами.

Использование sql совместно с объектами позволяет написать код, который будет значительно оптимальнее использовать ресурсы вашего сервера. Но еще более важно, что такой подход позволяет принести в ваши объекты все сильные стороны sql.

Posted in Laravel, Базы данных on May 18, 2016

comments powered by Disqus