Fluent - создание запросов к БД

Fluent — одна из библиотек, которой Laravel помогает мне отразить «серебряную пулю SQL» — хотя вы по прежнему можете писать запросы, если вам нравится это занятие. Что то лучшее, что даёт мне Fluent? Если не считать отсутствия SQL, то это использование подготовленных запросов («prepared statements»), которые полностью защищены от SQL-инъекций («SQL injection»).

Fluent понимает множество разных диалектов SQL, поэтому ваши запросы будут работать на самых разных БД. Перед тем, как мы начнём, познакомьтесь с механизмом цепочки методов. Возьмём этот пример:

Class::make()->chain()->chain()->chain()->trigger();

С помощью «сцепления» методов вместе мы можем строить SQL-запросы удобным и простым способом (как вы увидите позже). Экземпляр класса создаётся методом PHPmake(); иногда вам нужно будет передать ему параметры для инициализации; методы PHPchain() используются для изменения запроса через набор опций, а метод PHPtrigger() выдаёт конечный результат.

Это всё может звучать запутанно, но давайте посмотрим на следующий пример использования класса Fluent:

$users = DB::table('users')->where('username', '=', 'dayle')->get();

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

sqlSELECT * FROM users WHERE username = 'dayle';

...и вернёт массив объектов-строк в результате его выполнения.

Метод PHPtable() создаёт объект таблицы с заданным именем, с которой мы будем работать. Метод PHPwhere() вызывается «прицеплением» к нему и задаёт критерии для WHERE. Последний метод PHPget() — метод-триггер, получающий все объекты-строки как результат выполнения SQL.

Так как PHPget() возвращает нам массив строк мы можем пройти по нему с помощью foreach:

foreach ($users as $user) {
  echo $user->email;
}

Как вы видите, запрошенные поля читаются из свойств объекта — цикл выведет список e-mail-адресов всех наших пользователей.

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

Fluent имеет и другие методы-триггеры для получения результата:

• get — его мы только что использовали, он возвращает массив объектов — результирующих строк с полями
• first — этот метод вернёт один объект-результат, который подошёл под критерии запроса
• find($id) — метод находит запись по её ID; это краткая форма для записи PHPwhere('id', '=', $id); возвращает один объект-результат
• only($fieldname) — возвращает значение одного поля, подходящего под запрос
• get(array(...)) — вы можете передать методу PHPget() массив полей, чтобы получить только их

Критерии (WHERE)

Теперь, когда мы знаем, как получать результаты запросов, нужно разобраться, как устанавливать его критерии. В примере выше мы использовали PHPwhere() — посмотрим на него более детально:

$users = DB::table('users')->where('username', '=', 'dayle')->get();

Итак, снова этот маленький фрагмент кода, но на этот раз мы сконцентрируемся на части PHPwhere():

where('username', '=', 'dayle')

Одна из приятных особенностей в работе с цепочками «where» в Laravel — то, что они выглядят как настоящий SQL-запрос. В нашем случае мы бы написали: WHERE username = 'dayle' — первый параметр задаёт имя поля, которое мы сравниваем, второй задаёт оператор, а третий — значение, с которым выполняется сравнения.

Мы моглы бы написать и так:

where('age', '>', '18')
// WHERE age > '18'
// "Пей до дна! Хотя нет, если вы в Америке, то лучше не стоит..."

Что, если нам нужно больше условий? Для начала мы должны решить, нужно ли нам AND (и) или OR (или). В первом случае в цепочке методов просто снова используем PHPwhere(), например:

$users = DB::table('users')
           ->where('username', '=', 'dayle')
           ->where('sexyness', '>', 5000)
           ->get();

Как вы видите, я поместил вызов каждого метода на новой строке — мне такой способ записи кажется наиболее простым для чтения, в добавок он не создаёт чрезмерно длинных строк. Пример выше выполнит следующий SQL-запрос:

sqlSELECT * FROM users WHERE username = 'dayle' AND sexyness > 5000;

Если же нам нужно условие OR (или), то просто используем метод PHPor_where(), принимающий те же параметры:

$users = DB::table('users')
           ->where('username', '=', 'dayle')
           ->or_where('face', 'LIKE', '%malemodel%')
           ->get();

...что даст нам:

sqlSELECT * FROM users WHERE username = 'dayle' OR face LIKE '%malemodel%';

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

Методы PHPwhere_in(), PHPwhere_not_in(), PHPor_where_in() и PHPor_where_not_in() проверяют значение на принадлежность в определённому набору значений.

Методы PHPwhere_null(), PHPwhere_not_null(), PHPor_where_null() и PHPor_where_not_null() проверяют поле на соответствие NULL.

В SQL, NULL (поле без заданного значения) можно сравнивать только используя специальный оператор IS [NOT] NULL — обычные операторы (= и !=) не сработают. — прим. пер.

Иногда вам нужно будет задать вложенные критерии — Laravel позволяет это делать с помощью вложенных выражений WHERE. Посмотрим на пример из официальной документации:

$users = DB::table('users')
           ->where('id', '=', 1)
           ->or_where(function ($query) {
             $query->where('age', '>', 25);
             $query->where('votes' '>', 100);
           })
           ->get();

Передавая анонимную функцию («closure») с дополнительными критериями мы можем создавать вложенные WHERE. Вот что мы увидим в SQL:

sqlSELECT * FROM "users" WHERE "id" = ? OR ("age" > ? AND "votes" > ?)

Красиво, верно?

А теперь я покажу вам ещё одну замечательную возможность! (Я ведь говорил, насколько я не люблю SQL?) Динамические условия — необычный способ задания простых критериев. Взгляните:

where_size(5)->get();

Здесь мы указываем имя поля для проверки в имени самого вызываемого метода — Fluent достаточно умён, чтобы разобраться с этим.

Он даже понимает AND и OR:

where_size_and_height(700, 400)->get();

Выразительный и чистый — Laravel в своём лучшем виде.

Связывание таблиц (JOIN)

Давайте посмотрим на пример связывания таблиц во Fluent:

DB::table('tasks')
  ->join('project', 'tasks.id', '=', 'project.task_id')
  ->get(array('task.name', 'project.name'));

Здесь мы передаём имя таблицы в первом параметре, а остальные три задают условие для ON — так же, как и при определении WHERE.

Затем мы передаём список полей для получения в метод PHPget().

Таким же образом вызывается PHPleft_join(), используя те же параметры. Всё просто, верно?

Помните наш пример со вложенными WHERE? Мы можем использовать похожий приём для комбинации нескольких условий для ON:

  DB::table('tasks')
    ->join('project', function ($join) {
      $join->on('tasks.id', '=', 'project.task_id');
      $join->or_on('tasks.author_id', '=', 'project.author_id');
    })
    ->get(array('task.name', 'project.name'));

Здесь мы передаём анонимную функцию во втором параметре PHPjoin(), из которой можем вызывать PHPon(), PHPor_on() и PHPand_on() для задания критериев связывания.

Сортировка (ORDER)

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

DB::table('shoes')->order_by('size', 'asc')->get();

Хм... туфли? Женщины обычно думаю о туфлях, но это всё, что пришло мне на ум... Ужасно. Однако как бы там ни было, чтобы упорядочить результаты мы просто передаём методу PHPorder_by() имя поля и либо asc («ascending») для указания прямого порядка сортировки, либо desc («descending») для обратного. Для сортировки по нескольким полям просто «прицепите» ещё несколько PHPorder_by().

Ограничение (LIMIT)

Для получения ограниченного числа строк из результата SQL использует LIMIT, но это звучит немного странно. Laravel даёт нам take():

DB::table('shoes')->take(10)->get();

Теперь я хочу 10 туфлей? Мне нужно быть более вдумчивым... Однако всё и так понятно — нужное количество выбирается проще простого.

Пропуск результатов

Мы ведь не хотим получить первые 5 пар туфлей, верно? Они все кожаные, а я обожаю кроссовки — у меня широкая нога... Поэтому мы их пропустим:

DB::table('shoes')->skip(5)->get();

Вот и всё, теперь мы можем пропустить первые 5 результатов!

Групповые функции

Иногда бывает полезным выполнить простые подсчёты — AVG, MIN, MAX, SUM и COUNT очень часто используются для получения нужного результата в SQL — и, конечно, они доступны нам через Fluent:

// вычисление среднего значения среди всех значений поля "size":
$val = DB::table('shoes')->avg('size');
// минимальное значение:
$val = DB::table('shoes')->min('size');
// максимальное значение:
$val = DB::table('shoes')->max('size');
// сумма всех значений:
$val = DB::table('shoes')->sum('size');
// подсчёт числа строк в результате:
$val = DB::table('shoes')->count();

Так просто! Не забудьте, что это методы-триггеры, поэтому нам не нужно добавлять после них PHPget() — однако вы по прежнему можете использовать условия, ограничение, сортировку или любые другие функции Fluent.

Отмена экранированния

До сих пор все методы, которые мы использовали, автоматически экранировали наши параметры, но что если мы хотим сделать нечто нестандартное? Для этого есть метод PHPDB::raw():

DB::table('shoes')->update(array('worn' => DB::raw('NOW()')));

В этом запросе NOW() не будет помещён внутри кавычек, что даёт нам большую свободу в наших запросах, однако не забывайте о той известной цитате — будь ответственен, силой владеющий!

Увеличение/уменьшение

Fluent предоставляет два метода для простого инкремента/декремента значений выбранного поля:

DB::table('shoes')->increment('size');
DB::table('shoes')->decrement('size');

Просто передайте им имя и — вуаля!

Добавление записей

Наконец-то пришло время добавить новую запись. Всё это время мы говорили только о чтении данных, так что это будет более интересно — и просто: всё, что нам нужно для вставки записи в таблицу — передать методу PHPinsert() массив вида PHPполе => значение, который к тому же вляется методом-триггером:

DB::table('shoes')->insert(array(
  'color' => 'hot pink',
  'type'  => 'heels',
  'size'  => '12'
));

Иногда нам может пригодится ID только что добавленной записи — для этого используется PHPinsert_get_id() в теми же параметрами:

$id = DB::table('shoes')->insert_get_id(array(
  'color' => 'hot pink',
  'type'  => 'heels',
  'size'  => '12'
));

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

Изменение записей

Постойте, я и правда сказал «каблуков» в предыдущем разделе? Это были розовые кроссовки — но если бы мы только могли вернуться и исправить ошибку, я мог бы изменить запись и никто бы не узнал об этом досадном инциденте. Хотя... я думаю мы можем воспользоваться методом PHPupdate() — он принимает всё тот же массив значений, что и PHPinsert():

DB::table('shoes')->update(array(
  'type' => 'skate shoes'
));

Подождите, мы не указываем здесь, что именно нужно обновить? Я ведь не хочу превратить всю свою обувь в кроссовки! Давайте используем уже знакомый нам метод PHPwhere() и тот PHP$id, что мы получили в примере выше. Время для «цепочки»:

DB::table('shoes')
  ->where('id', '=', $id)
  ->update(array(
    'type' => 'skate shoes'
  ));

Отлично, моя оплошность исправлена ещё до того, как кто-то успел её заметить.

Удаление записей

Для удаления (записей, а не кроссовок — они ведь не виновны!) используется PHPdelete() с соответствующими критериями PHPwhere(). Мы также можем просто передать ему нужный ID для удаления одной записи. Посмотрим на него в действии:

DB::table('not_shoes')->where('texture', '=', 'fuzzy')->delete();

Отлично, мы удалили нечто туманное («fuzzy»).

DB::table('shoes')->delete($id);

О нет! Это ведь не те самые розовые кроссовки? Определённо, вы узнали слишком много — пожалуйста, помните об ответственности и силе... мы ведь больше не хотим истреблений мирных туфлей.

В моей следующей статье я расскажу о туф... об Eloquent. Eloquent позволяет нам работать с базой данных как с набором объектов с помощью элегантного механизма зависимости между полями и записями в таблицах.