Конструктор запросов

Query Builder → Community +860 31 мая 2015

Конструктор запросов Laravel предоставляет удобный, выразительный интерфейс для создания и выполнения запросов к базе данных. Он может использоваться для выполнения большинства типов операций и работает со всеми поддерживаемыми СУБД .

Конструктор запросов Laravel использует привязку параметров к запросам средствами PDO для защиты вашего приложения от SQL-инъекций. Нет необходимости экранировать строки перед их передачей в запрос.

Получение результатов

Получение всех записей таблицы

Используйте метод PHP table () фасада DB для создания запроса. Метод PHP table () возвращает экземпляр конструктора запросов для данной таблицы, позволяя вам «прицепить» к запросу дополнительные условия и в итоге получить результат методом PHP get () :

Метод PHP get () возвращает объект Illuminate\Support\Collection (для версии 5.2 и ранее — массив) c результатами, в котором каждый результат — это экземпляр PHP-объекта StdClass . Вы можете получить значение каждого столбца, обращаясь к столбцу как к свойству объекта:

Получение одной строки/столбца из таблицы

Если вам необходимо получить только одну строку из таблицы БД, используйте метод PHP first () . Этот метод вернёт один объект StdClass :

Если вам не нужна вся строка, вы можете извлечь одно значение из записи методом PHP value () . Этот метод вернёт значение конкретного столбца:

Получение списка всех значений одного столбца

Если вы хотите получить массив значений одного столбца, используйте метод PHP pluck () . В этом примере мы получим коллекцию (для версии 5.2 и ранее — массив) названий ролей:

Вы можете указать произвольный ключ для возвращаемой коллекции (для версии 5.2 и ранее — массива):

Если вы хотите получить массив значений одного столбца, используйте метод PHP lists () . В этом примере мы получим массив названий ролей:

Вы можете указать произвольный ключ для возвращаемого массива:

Получение результатов из таблицы «по кускам»

Если вам необходимо обработать тысячи записей БД, попробуйте использовать метод PHP chunk () . Этот метод получает небольшой «кусок» результатов за раз и отправляет его в замыкание для обработки. Этот метод очень полезен для написания Artisan-команд, которые обрабатывают тысячи записей. Например, давайте обработаем всю таблицу users «кусками» по 100 записей:

Вы можете остановить обработку последующих «кусков» вернув false из замыкания:

Агрегатные функции

Конструктор запросов содержит множество агрегатных методов, таких как count, max, min, avg и sum. Вы можете вызывать их после создания своего запроса:

Разумеется, вы можете комбинировать эти методы с другими условиями:

Выборка ( SELECT )

Указание столбцов для выборки

Само собой, не всегда вам необходимо выбрать все столбцы из таблицы БД. Используя метод PHP select () вы можете указать необходимые столбцы для запроса:

Метод PHP distinct () позволяет вернуть только отличающиеся результаты:

Если у вас уже есть экземпляр конструктора запросов и вы хотите добавить столбец к существующему набору для выборки, используйте метод PHP addSelect () :

Сырые выражения

Иногда вам может понадобиться использовать уже готовое SQL-выражение в вашем запросе. Такие выражения вставляются в запрос напрямую в виде строк, поэтому будьте внимательны и не допускайте возможностей для SQL-инъекций! Для создания сырого выражения используйте метод PHP DB :: raw () :

Объединения ( JOIN )

Конструктор запросов может быть использован для объединения данных из нескольких таблиц через PHP JOIN . Для выполнения обычного объединения «inner join» , используйте метод PHP join () на экземпляре конструктора запросов. Первый аргумент метода PHP join () — имя таблицы, к которой необходимо присоединить другие, а остальные аргументы указывают условия для присоединения столбцов. Как видите, вы можете объединять несколько таблиц одним запросом:

Для выполнения объединения «left join» вместо «inner join» , используйте метод PHP leftJoin () . Этот метод имеет ту же сигнатуру, что и метод PHP join () :

Для выполнения объединения CROSS JOIN используйте метод PHP crossJoin () с именем таблицы, с которой нужно произвести объединение. CROSS JOIN формирует таблицу перекрестным соединением (декартовым произведением) двух таблиц:

Сложные условия объединения

Вы можете указать более сложные условия для объединения. Для начала передайте замыкание вторым аргументом метода PHP join () . Замыкание будет получать объект JoinClause , позволяя вам указать условия для объединения:

Если вы хотите использовать стиль «where» для ваших объединений, то можете использовать для этого методы PHP where () и PHP orWhere () . Вместо сравнения двух столбцов эти методы будут сравнивать столбец и значение:

Слияние ( UNION )

Конструктор запросов позволяет создавать слияния двух запросов вместе. Например, вы можете создать начальный запрос и с помощью метода PHP union () слить его со вторым запросом:

Также существует метод PHP unionAll () с аналогичными параметрами.

Условия WHERE

Для добавления в запрос условий where используйте метод PHP where () на экземпляре конструктора запросов. Самый простой вызов PHP where () требует три аргумента. Первый — имя столбца. Второй — оператор (любой из поддерживаемых базой данных). Третий — значение для сравнения со столбцом.

Например, вот запрос, проверяющий равенство значения столбца «votes» и 100:

Для удобства, если вам необходимо просто проверить равенство значения столбца и данного значения, вы можете передать значение сразу вторым аргументом метода PHP where () :

Разумеется, вы можете использовать различные другие операторы при написании условия where :

В функцию PHP where () также можно передать массив условий:

Вы можете сцепить вместе условия where , а также условия or в запросе. Метод PHP orWhere () принимает те же аргументы, что и метод PHP where () :

Дополнительные условия WHERE

Метод PHP whereBetween () проверяет, что значения столбца находится в указанном интервале:

Метод PHP whereNotBetween () проверяет, что значения столбца находится вне указанного интервала:

Фильтрация по совпадению с массивом значений

Метод PHP whereIn () проверяет, что значения столбца содержатся в данном массиве:

Метод PHP whereNotIn () проверяет, что значения столбца не содержатся в данном массиве:

Поиск неустановленных значений ( NULL )

Метод PHP whereNull () проверяет, что значения столбца равны PHP NULL :

Метод PHP whereNotNull () проверяет, что значения столбца не равны PHP NULL :

whereDate / whereMonth / whereDay / whereYear

Метод PHP whereDate () служит для сравнения значения столбца с датой:

Метод PHP whereMonth () служит для сравнения значения столбца с месяцем в году:

Метод PHP whereDay () служит для сравнения значения столбца с днём месяца:

Метод PHP whereYear () служит для сравнения значения столбца с указанным годом:

Динамические условия WHERE

Вы можете использовать даже «динамические» условия where для гибкого построения операторов, используя магические методы:

Для проверки на совпадение двух столбцов можно использовать метод PHP whereColumn () :

В метод также можно передать оператор сравнения:

В метод PHP whereColumn () также можно передать массив с несколькими условиями. Эти условия будут объединены оператором AND :

Группировка условий

Иногда вам нужно сделать выборку по более сложным параметрам, таким как «существует ли» или вложенная группировка условий. Конструктор запросов Laravel справится и с такими запросами. Для начала посмотрим на пример группировки условий в скобках:

Как видите, передав замыкание в метод PHP orWhere () , мы дали конструктору запросов команду, начать группировку условий. Замыкание получит экземпляр конструктора запросов, который вы можете использовать для задания условий, поместив их в скобки. Приведённый пример выполнит такой SQL-запрос:

Проверка на существование

Метод PHP whereExists () позволяет написать SQL-условие where exists . Метод PHP whereExists () принимает в качестве аргумента замыкание, которое получит экземпляр конструктора запросов, позволяя вам определить запрос для помещения в условие «exists» :

Этот пример выполнит такой SQL-запрос:

JSON фильтрация ( WHERE )

Laravel также поддерживает запросы для столбцов типа JSON в тех БД, которые поддерживают тип столбцов JSON. На данный момент это MySQL 5.7 и Postgres. Для запроса JSON столбца используйте оператор -> :

Упорядочивание, группировка, предел и смещение

Метод PHP orderBy () позволяет вам отсортировать результат запроса по заданному столбцу. Первый аргумент метода PHP orderBy () — столбец для сортировки по нему, а второй — задаёт направление сортировки и может быть либо asc , либо desc :

Методы PHP latest () и PHP oldest () позволяют легко отсортировать результаты по дате. По умолчанию выполняется сортировка по столбцу created_at . Или вы можете передать имя столбца для сортировки по нему:

Для сортировки результатов запроса в случайном порядке можно использовать метод PHP inRandomOrder () . Например, вы можете использовать этот метод для выбора случайного пользователя:

Методы PHP groupBy () и PHP having () используются для группировки результатов запроса. Сигнатура метода PHP having () аналогична методу PHP where () :

Метод PHP havingRaw () используется для передачи сырой строки в условие having . Например, мы можем найти все филиалы с объёмом продаж выше $2,500:

Для ограничения числа возвращаемых результатов из запроса или для пропуска заданного числа результатов в запросе используются методы PHP skip () и PHP take () :

Или вы можете использовать методы PHP limit () и PHP offset () :

Условное применение условий

Иногда необходимо применять условие к запросу, только если выполняется какое-то другое условие. Например, выполнять оператор PHP where , только если нужное значение есть во входящем запросе. Это можно сделать с помощью метода PHP when () :

Метод PHP when () выполняет данное замыкание, только когда первый параметр равен PHP true . Если первый параметр равен PHP false , то замыкание не будет выполнено.

Вы можете передать ещё одно замыкание третьим параметром метода PHP when () . Это замыкание будет выполнено, если первый параметр будет иметь значение PHP false . Для демонстрации работы этой функции мы используем её для настройки сортировки по умолчанию для запроса:

Вставка ( INSERT )

Конструктор запросов предоставляет метод PHP insert () для вставки записей в таблицу БД. Метод PHP insert () принимает массив имён столбцов и значений:

Вы можете вставить в таблицу сразу несколько записей одним вызовом PHP insert () , передав ему массив массивов, каждый из которых — строка для вставки в таблицу:

Если в таблице есть автоинкрементный ID, используйте метод PHP insertGetId () для вставки записи и получения её ID:

При использовании метода PHP insertGetId () для PostgreSQL автоинкрементное поле должно иметь имя PHP id . Если вы хотите получить ID из другого поля таблицы, вы можете передать его имя вторым аргументом.

Обновление ( UPDATE )

Разумеется, кроме вставки записей в БД конструктор запросов может и изменять существующие строки с помощью метода PHP update () . Метод PHP update () , как и метод PHP insert () , принимает массив столбцов и пар значений, содержащих столбцы для обновления. Вы можете ограничить запрос PHP update () условием PHP where () :

Обновление JSON-столбцов

При обновлении JSON-столбцов используйте синтаксис PHP -> для обращения к нужному ключу в JSON-объекте. Эта операция поддерживается только в БД, поддерживающих JSON-столбцы:

Increment и Decrement

Конструктор запросов предоставляет удобные методы для увеличения и уменьшения значений заданных столбцов. Это просто более выразительный и краткий способ по сравнению с написанием оператора update вручную.

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

Вы также можете указать дополнительные поля для изменения:

Удаление ( DELETE )

Конструктор запросов предоставляет метод PHP delete () для удаления записей из таблиц. Вы можете ограничить оператор PHP delete () , добавив условие PHP where () перед его вызовом:

Если вы хотите очистить таблицу (усечение), удалив все строки и обнулив счётчик ID, используйте метод PHP truncate () :

Усечение таблицы аналогично удалению всех её записей, а также сбросом счётчика autoincrement-полей. — прим. пер.

Пессимистическая блокировка

В конструкторе запросов есть несколько функций, которые помогают делать «пессимистическую блокировку» (pessimistic locking) для ваших операторов SELECT. Для запуска оператора SELECT с «разделяемой блокировкой» вы можете использовать в запросе метод PHP sharedLock () . Разделяемая блокировка предотвращает изменение выбранных строк до конца транзакции:

Или вы можете использовать метод PHP lockForUpdate () . Блокировка «для изменения» предотвращает изменение строк и их выбор другими разделяемыми блокировками:

Статистика: Символов — 29 326/24 354 без пробелов (11 645/10 008 без кода):, слов — 1 638