SavePearlHarbor

Построитель SQL запросов на основе мета-информации миграций БД

—

от автора

admin

В век когда ORM шагает по планете обычный построитель запросов выглядит откатом назад. Однако тут есть нюанс — Sql Query Builder использует пакет версионирования shasoft/db-schema и владеет всей информацией о структуре базы данных. Это позволяет реализовать все стандартные для таких решений функции, прозрачно конвертировать типы данных SQL<=>PHP + реализовать нестандартные возможности в виде выборки данных с использованием КЭШирования. (Просьба не искать логику в SQL запросах в статье и примерах, её там нет. Искусственные примеры предназначены для демонстрации возможностей пакета и никакой другой смысловой нагрузки не несут).

Стандартные функции

  • Работа напрямую с драйвером PDO. Поддерживаются:
  • Поддержка команд SELECT, INSERT, UPDATE, DELETE
  • Соединения JOIN (INNER, LEFT, RIGHT)
  • Подзапросы в условиях фильтрации (в том числе многостолбцовые подзапросы)
  • Включение условий фильтрации по условию
  • Исключение дубликатов, DISTINCT
  • Сортировка, оператор ORDER BY
  • Группировка, оператор GROUP BY
  • Агрегатные функции
  • Оператор HAVING
  • Ограничение выборки, оператор LIMIT
  • Поддержка пагинации (возвращается объект пагинации который содержит всю информацию (текущая страница, общее количество страниц и т.д.))

Кому проще смотреть код, а не читать инструкцию могут посмотреть стандартные примеры.

Создание объекта построителя запросов

//-- Миграции // Получить миграции $migrations = DbSchemaMigrations::get(     [         User::class,         UserInfo::class,         Article::class,         Comment::class,         TabNoPrimaryKey::class     ],     DbSchemaDriverMySql::class ); // Выполнить дополнительный расчет BuilderExtraData::run($migrations); // Выполнить миграции $migrations->run($pdo);  //-- Создать построитель запросов $builder =  new Builder(     $pdo,                          // PDO драйвер     $migrations->database()        // Информация о БД     [, new CacheItemPoolInterface] // Необязательный параметр );

Само собой в рабочем проекте миграции не будут выполняться каждый раз при создании построителя запроса. Сами миграции достаточно выполнить отдельно и сохранить готовую схему БД. При создании построителя запросов использовать сохраненную схему.

Некоторые важные отличия от общепринятого синтаксиса

  1. Условия фильтрации указываются только через замыкание.

    $builder ->select(Table1::class, ['column1', 'column2',...]) ->where(function (Where $where) {     // Тут указываются все условия фильтрации     // Поддерживается вложенный блок     $where         ->cond('id', '>', 1)         ->inSelect(             ['column1'],             Table2::class,             ['column1', 'column2']         )->where(function(Where $where) {             // вложенный блок             // ..         }); });

  2. Условия соединения указываются только через замыкание.

    $builder ->select(Table1::class, ['column1', 'column2',...]) ->join(function (Join $join) {     // В данном замыкании указываются все соединения     // (таких замыканий может быть несколько)     $join->left(Table2::class, [         'userId' => 'id'     ], [         'column'     ]); });

  3. Использование отношений для генерации соединений.
    Так как имеется информация о структуре БД, то вместо указания таблицы и полей связи можно использовать отношение для создания соединения.

    $rows = $builder ->select(Comment::class, ['id', 'createAt']) ->where(function (Where $where) {     $where->inArray('id', [1, 3, 5]); }) ->relation('article', ['title'], function (JoinItem $joinItem) {     $joinItem->relation('author', ['name as authorArticleName']); }) ->relation('author', ['name as authorCommentName']) ->get();

  4. Контекст таблицы.
    Предположим у нас есть следующая выборка: выбираем комментарий, к нему родительскую статью, к статье имя автора статьи.

    SELECT `COMMENT_1`.`text`, `ARTICLE_1`.`title`, `USER_1`.`name` FROM `comment` `COMMENT_1` LEFT JOIN `article` `ARTICLE_1` ON `ARTICLE_1`.`id` = `COMMENT_1`.`articleId` LEFT JOIN `user` `USER_1` ON `USER_1`.`id` = `ARTICLE_1`.`userId`

    Все это реализуется через следующий PHP код:

    $builder ->select(Comment::class, ['text']) ->join(function (Join $joinComment) {     // Переменная $joinComment имеет контекст таблицы = Comment::class     $joinArticle = $joinComment->left(         Article::class,         ['id' => 'articleId'],         ['title']     );     // Переменная $joinArticle имеет контекст таблицы = Article::class     $joinUser = $joinArticle->left(         User::class,         ['id' => 'userId'],         ['name']     );     // Переменная $joinUser имеет контекст таблицы = User::class })->get();

    При создании соединения все связующие поля по умолчанию считаются принадлежащими этому контексту. В примере выше при создании соединения $joinUser мы указываем поле userId и это означает что это поле контекста $joinArticle (т.е. поле таблицы Article).
    Если мы перепишем в таком вот виде (изменим контекст при создании соединения с User):

    $builder ->select(Comment::class, ['text']) ->join(function (Join $joinComment) {     // Переменная $joinComment имеет контекст таблицы = Comment::class     $joinArticle = $joinComment->left(         Article::class,         ['id' => 'articleId'],         ['title']     );     // Переменная $joinComment имеет контекст таблицы = Comment::class     $joinUser = $joinComment->left(         User::class,         ['id' => 'userId'],         ['name']     );     // Переменная $joinUser имеет контекст таблицы = User::class })->get();

    то получим имя автора комментария и вот такой SQL запрос:

    SELECT `COMMENT_1`.`text`, `ARTICLE_1`.`title`, `USER_1`.`name` FROM `comment` `COMMENT_1` LEFT JOIN `article` `ARTICLE_1` ON `ARTICLE_1`.`id` = `COMMENT_1`.`articleId` LEFT JOIN `user` `USER_1` ON `USER_1`.`id` = `COMMENT_1`.`userId`

    Также используя контекст таблицы можно получить имя поля таблицы этого контекста. К примеру SQL запрос выше можно получить через вот такой PHP код:

    $builder ->select(Comment::class, ['text']) ->join(function (Join $joinComment) {     // Переменная $join имеет контекст таблицы = Comment::class     $joinArticle = $joinComment->left(         Article::class,         ['id' => 'articleId'],         ['title']     );     // Переменная $joinArticle имеет контекст таблицы = Article::class     $joinUser = $joinArticle->left(         User::class,         ['id' => $join->column('userId')], // Указываем поле контекста         ['name']     );     // Переменная $joinUser имеет контекст таблицы = User::class })->get();

    Тут мы создаем соединение через контекст таблицы Article, но в условиях связи указываем поле нужного нам контекста, а именно поле userId таблицы комментария Comment. В результате сгенерируется тот же самый SQL код для выбора автора комментария.

Теперь, когда на примере стало понятно, как что такое контекст таблицы и как он работает, раскрою вам реальную причину создания такого функционала. Он нужен чтобы выполнять соединения одной таблицы с несколькими. При указании такой связи можно для каждого поля исходной таблицы указать поле другой таблицы в соответствующем ему контексте.
К примеру вот такой код:

$builder     ->select(Comment::class, ['text'])     ->join(function (Join $joinComment) {         // Переменная $join имеет контекст таблицы = Comment::class         $joinArticle = $joinComment->left(             Article::class,             ['id' => 'articleId'],             ['title']         );         // Переменная $joinArticle имеет контекст таблицы = Article::class         $joinComment->left(             User::class,             [                 'id' => $joinComment->column('userId'),                 'age' => $joinArticle->column('id')             ],             ['name']         );     })->get();

сгенерирует вот такой SQL код

SELECT     `COMMENT_1`.`text`,     `ARTICLE_1`.`title`,     `USER_1`.`name` FROM     `comment` `COMMENT_1` LEFT JOIN `article` `ARTICLE_1` ON     `ARTICLE_1`.`id` = `COMMENT_1`.`articleId` LEFT JOIN `user` `USER_1` ON     `USER_1`.`id` = `COMMENT_1`.`userId` AND `USER_1`.`age` = `ARTICLE_1`.`id`

Чтобы минимизировать передачу переменной контекста в нужное замыкание можно использовать именованный контекст таблицы. Для этого необходимо вызвать метод name() соответствующего контекста и указать его имя. В дальнейшем при использовании метода column() контекста вторым параметром указываем нужное имя. Пример:

$builder->select(User::class, ['name'])     ->name('main') // Устанавливаем имя контекста     ->where(function (Where $where) {         $where->inSelect(             'id',             Article::class,             'userId',             function (Select $select) {                 $select                     ->where(function (Where $where) {                         $where->cond(                             'id',                             '=',                             // Используемый именованный контекст                             $where->column('id', 'main')                         );                     });             }         );     })     ->get();

Сгенерированный SQL код. 

SELECT     `USER_1`.`name` FROM     `user` `USER_1` WHERE     `USER_1`.`id` IN(     SELECT         `ARTICLE_1`.`userId`     FROM         `article` `ARTICLE_1`     WHERE         `ARTICLE_1`.`id` = `USER_1`.`id` )

Мы установили имя для контекста таблицы User и затем использовали его в условии фильтрации. И да, контекст таблицы можно использовать не только в соединениях, но и в условиях фильтрации.

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

НеСтандартная функция заполнения

Кому проще смотреть код, а не читать инструкцию могут сразу посмотреть примеры заполнения.

К примеру у нас стоит задача: выбрать по идентификаторам список комментариев. К каждому из них выбрать автора комментария, родительскую статью и автора статьи.

При использования КЭШирования необходимо выполнить следующие шаги:

  1. Выбрать список уникальных идентификаторов статей
  2. Выбрать список комментариев по которым нет информации в КЭШе
  3. Сохранить в КЭШ все отсутствующие там данные по выбранным комментариям
  4. Выбрать список уникальных идентификаторов родительских статей
  5. Выбрать список статей по которым нет информации в КЭШе
  6. Сохранить в КЭШ все отсутствующие там данные по выбранным статьям
  7. Выбрать список уникальных идентификаторов пользователей авторов комментариев и статей
  8. Выбрать список пользователей по которым нет информации в КЭШе
  9. Сохранить в КЭШ все отсутствующие там данные по выбранным пользователям
  10. Записать все выбранные данные в исходные строки

Все эти шаги можно выполнить с использованием функции fill(), которая прячем под капотом все приведенные выше пункты:

// Список строк с идентификаторами комментариев $rows = [     ['id' => 1],     ['id' => 3],     ['id' => 5],     ['id' => 3], ]; $fill = $builder     // Вызываем функцию и указываем строки для заполнения     ->fill($rows)     // Выбрать данные из таблицы с комментариями     ->fromTable(Comment::class, ['id'], ['text'], function (From $fromComment) {         // Выбрать автора комментария         $fromComment->joinTable(             User::class,             ['id' => 'userId'],             ['name as authorCommentName']         );         // Выбрать родительскую статью         $fromUserArticle = $fromComment->joinTable(             Article::class,             ['id' => 'articleId'],             ['title']         );         // Выбрать автора статьи         $fromUserArticle->joinTable(             User::class,             ['id' => 'userId'],             ['name as authorArticleName']         );     });

В результате будет сгенерирован следующий код для трех SQL запросов

SELECT     * FROM     `comment` WHERE     `id` IN(1, 3, 5) SELECT     * FROM     `article` WHERE     `id` IN(8, 10) SELECT     * FROM     `user` WHERE     `id` IN(10, 3, 9, 6, 7)

В каждую исходную строку будут записаны поля: text, authorCommentName, title, authorArticleName с соответствующими значениями. Все результаты выборок будут сохранены в КЭШ. При внесении изменений в соответствующие строки БД через команды UPDATE и DELETE соответствующие значения в КЭШе будут удаляться.

При этом данная функция будет работать и при отключенном КЭШе. В этом случае будет генерироваться следующий SQL код:

SELECT     `COMMENT_1`.`text`,     `COMMENT_1`.`id` AS `~~~id`,     `ARTICLE_1`.`title`,     `USER_1`.`name` AS `authorCommentName`,     `USER_2`.`name` AS `authorArticleName` FROM     `comment` `COMMENT_1` LEFT JOIN `article` `ARTICLE_1` ON     `ARTICLE_1`.`id` = `COMMENT_1`.`articleId` LEFT JOIN `user` `USER_1` ON     `USER_1`.`id` = `COMMENT_1`.`userId` LEFT JOIN `user` `USER_2` ON     `USER_2`.`id` = `ARTICLE_1`.`userId` WHERE     `COMMENT_1`.`id` IN(1, 3, 5)

и в результате работы функции fill() заполняемые строки с данными будут содержать те же самые данные что и при включенном КЭШе. Это позволяет писать один и тот же код в независимости от того используется КЭШ или нет.

Некоторые нереализованные штуки

  1. Нет поддержки соединения вида FULL так как в MySql этот вид соединения не поддерживается. Убрал для совместимости.
  2. Не поддерживаются соединения в DELETE так как PostgreSQL не поддерживает такого. Убрал для совместимости.
  3. Не поддерживаются соединения в UPDATE так как PostgreSQL не поддерживает такого. Убрал для совместимости.
  4. Функция fill() на текущий момент не поддерживает многоколоночные первичные ключи. Отложил реализацию на будущее.


ссылка на оригинал статьи https://habr.com/ru/articles/788098/

Комментарии

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *