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

Статистика: Символов — 12 693/10 579 без пробелов (8 339/7 031 без кода):, слов — 1 309
Разметка: Wiki
{{TOC}}

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

//Fluent// понимает множество разных диалектов SQL, поэтому ваши запросы будут работать на самых разных БД. Перед тем, как мы начнём, познакомьтесь с механизмом **цепочки методов**. Возьмём этот пример:
%%
  Class::make()->chain()->chain()->chain()->trigger();
%%

С помощью "сцепления" методов вместе мы можем строить SQL-запросы удобным и простым способом (как вы увидите позже). Экземпляр класса создаётся методом %%make()%%; иногда вам нужно будет передать ему параметры для инициализации; методы %%chain()%% используются для изменения запроса через набор опций, а метод %%trigger()%% выдаёт конечный результат.

Это всё может звучать запутанно, но давайте посмотрим на следующий пример использования класса //Fluent//:
%%
  $users = DB::table('users')->where('username', '=', 'dayle')->get();
%%

Этот пример выполнит простой запрос:
%%(sql)
  SELECT * FROM users WHERE username = 'dayle';
%%

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

Метод %%table()%% создаёт объект таблицы с заданным именем, с которой мы будем работать. Метод %%where()%% вызывается "прицеплением" к нему и задаёт критерии для %%(t)WHERE%%. Последний метод %%get()%% - метод-триггер, получающий все объекты-строки как результат выполнения SQL.

Так как %%get()%% возвращает нам массив строк мы можем пройти по нему с помощью **foreach**:
%%
  foreach ($users as $user) {
    echo $user->email;
  }
%%

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

== ((#trig)) Получение результатов ==
//Fluent// имеет и другие методы-триггеры для получения результата:
  * **get** - его мы только что использовали, он возвращает массив объектов - результирующих строк с полями
  * **first** - этот метод вернёт один объект-результат, который подошёл под критерии запроса
  * **find($id)** - метод находит запись по её //ID//; это краткая форма для записи %%where('id', '=', $id)%%; **возвращает** один объект-результат
  * **only($fieldname)** - возвращает значение одного поля, подходящего под запрос
  * **get(array(...))** - вы можете передать методу %%get()%% массив полей, чтобы получить только их

== ((#where)) Критерии (WHERE) ==
Теперь, когда мы знаем, как получать результаты запросов, нужно разобраться, как устанавливать //его критерии//. В примере выше мы использовали %%where()%% - посмотрим на него более детально:
%%
  $users = DB::table('users')->where('username', '=', 'dayle')->get();
%%

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

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

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

Что, если нам нужно **больше условий**? Для начала мы должны решить, нужно ли нам **AND** (//и//) или **OR** (//или//). В первом случае в //цепочке методов// просто снова используем %%where()%%, например:
%%
  $users = DB::table('users')
             ->where('username', '=', 'dayle')
             ->where('sexyness', '>', 5000)
             ->get();
%%

Как вы видите, я поместил вызов каждого метода на новой строке - мне такой способ записи кажется наиболее простым для чтения, в добавок он не создаёт чрезмерно длинных строк. Пример выше выполнит следующий SQL-запрос:
%%(sql)
  SELECT * FROM users WHERE username = 'dayle' AND sexyness > 5000;
%%

Если же нам нужно условие **OR** (//или//), то просто используем метод %%or_where()%%, принимающий те же параметры:
%%
  $users = DB::table('users')
             ->where('username', '=', 'dayle')
             ->or_where('face', 'LIKE', '%malemodel%')
             ->get();
%%

...что даст нам:
%%(sql)
  SELECT * FROM users WHERE username = 'dayle' OR face LIKE '%malemodel%';
%%

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

Методы %%where_in()%%, %%where_not_in()%%, %%or_where_in()%% и %%or_where_not_in()%% проверяют значение на принадлежность в определённому набору значений.

Методы %%where_null()%%, %%where_not_null()%%, %%or_where_null()%% и %%or_where_not_null()%% проверяют поле на соответствие **NULL**.

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

Иногда вам нужно будет задать **вложенные критерии** - Laravel позволяет это делать с помощью //вложенных выражений %%(t)WHERE%%//. Посмотрим на пример из ((doc3:database/fluent#nested-where официальной документации)):
%%
  $users = DB::table('users')
             ->where('id', '=', 1)
             ->or_where(function ($query) {
               $query->where('age', '>', 25);
               $query->where('votes' '>', 100);
             })
             ->get();
%%

Передавая //анонимную функцию// ("closure") с дополнительными критериями мы можем создавать вложенные %%(t)WHERE%%. Вот что мы увидим в SQL:
%%(sql)
  SELECT * 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)) Связывание таблиц (JOIN) ==
Давайте посмотрим на пример связывания таблиц во //Fluent//:
%%
  DB::table('tasks')
    ->join('project', 'tasks.id', '=', 'project.task_id')
    ->get(array('task.name', 'project.name'));
%%

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

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

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

Помните наш пример со вложенными %%(t)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'));
%%

Здесь мы передаём //анонимную функцию// во втором параметре %%join()%%, из которой можем вызывать %%on()%%, %%or_on()%% и %%and_on()%% для задания критериев связывания.

== ((#order)) Сортировка (ORDER) ==
Сортировка - полезная штука, если вам не хочется делать это с помощью PHP. Вы, конечно, могли бы... множество хитрых %%sort()%%, десятки циклов - но это было бы не смешно. Давайте лучше положимся на //Fluent//:
%%
  DB::table('shoes')->order_by('size', 'asc')->get();
%%

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

== ((#limit)) Ограничение (LIMIT) ==
Для получения ограниченного числа строк из результата SQL использует %%(t)LIMIT%%, но это звучит немного странно. Laravel даёт нам **take()**:
%%
  DB::table('shoes')->take(10)->get();
%%

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

=== ((#skip)) Пропуск результатов ===
Мы ведь не хотим получить первые 5 пар туфлей, верно? Они все кожаные, а я обожаю кроссовки - у меня широкая нога... Поэтому мы их пропустим:
%%
  DB::table('shoes')->skip(5)->get();
%%

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

== ((#aggreg)) Групповые функции ==
Иногда бывает полезным выполнить простые подсчёты - **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();
%%

Так просто! Не забудьте, что это ((#trig методы-триггеры)), поэтому нам не нужно добавлять после них %%get()%% - однако вы по прежнему можете использовать ((#where условия)), ((#limit ограничение)), ((#order сортировку)) или любые другие функции //Fluent//.

== Отмена экранированния ==
До сих пор все методы, которые мы использовали, **автоматически экранировали наши параметры**, но что если мы хотим сделать нечто нестандартное? Для этого есть метод %%DB::raw()%%:
%%
  DB::table('shoes')->update(array('worn' => DB::raw('NOW()')));
%%

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

== Увеличение/уменьшение ==
//Fluent// предоставляет два метода для простого инкремента/декремента значений выбранного поля:
%%
  DB::table('shoes')->increment('size');
  DB::table('shoes')->decrement('size');
%%

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

== ((#insert)) Добавление записей ==
Наконец-то пришло время добавить новую запись. Всё это время мы говорили только о чтении данных, так что это будет более интересно - и просто: всё, что нам нужно для вставки записи в таблицу - передать методу %%insert()%% массив вида %%поле => значение%%, который к тому же вляется ((#trig методом-триггером)):
%%
  DB::table('shoes')->insert(array(
    'color' => 'hot pink',
    'type'  => 'heels',
    'size'  => '12'
  ));
%%

Иногда нам может пригодится ID только что добавленной записи - для этого используется %%insert_get_id()%% в теми же параметрами:
%%
  $id = DB::table('shoes')->insert_get_id(array(
    'color' => 'hot pink',
    'type'  => 'heels',
    'size'  => '12'
  ));
%%

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

== ((#update)) Изменение записей ==
Постойте, я и правда сказал "каблуков" в предыдущем разделе? Это были розовые кроссовки - но если бы мы только могли вернуться и исправить ошибку, я мог бы изменить запись и никто бы не узнал об этом досадном инциденте. Хотя... я думаю мы можем воспользоваться методом %%update()%% - он принимает всё тот же массив значений, что и %%insert()%%:
%%
  DB::table('shoes')->update(array(
    'type' => 'skate shoes'
  ));
%%

Подождите, мы не указываем здесь, что именно нужно обновить? Я ведь не хочу превратить //всю// свою обувь в кроссовки! Давайте используем ((#where уже знакомый нам)) метод %%where()%% и тот %%$id%%, что мы получили в примере выше. Время для "цепочки":
%%
  DB::table('shoes')
    ->where('id', '=', $id)
    ->update(array(
      'type' => 'skate shoes'
    ));
%%

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

== ((#delete)) Удаление записей ==
Для удаления (записей, а не кроссовок - они ведь не виновны!) используется %%delete()%% с соответствующими ((#where критериями)) %%where()%%. Мы также можем просто передать ему нужный ID для удаления одной записи. Посмотрим на него в действии:
%%
  DB::table('not_shoes')->where('texture', '=', 'fuzzy')->delete();
%%

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

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

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

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