Работа с БД в Drupal 7 - урок 4 - Динамические запросы (SELECT)

Пожалуй мы подобрались к самой интересной части Database API Drupal: динамические запросы. Динамические запросы называются так, потому что Drupal динамически дописывает строку запроса. Все запросы вставки, обновления, удаления или слияния могут быть динамическими. Запросы выборки могут быть как динамическими, так и статическими. Тем не менее, используйте динамические запросы и для запросов выборки.

Все динамические запросы создаются как объект-запрос, запрашиваем когда нам будет необходимо. Как и в статических запросах подавляющее большинство запросов будут использовать процедурную обертку (procedural wrapper). Все последующие параметры запроса могут быть добавлены в объект-запроса.

db_select() запросы выборки

Динамические запросы выборки начинаются с функции db_select(), например так:

<?php
$query = db_select('node', 'n', $options);
?>

В этом примере "node" это таблица к которой мы обращаемся, в SQL это соответствует записи после FROM. Заметьте что это имя таблицы не оборачиваются фигурными скобками как раньше. Конструктор запросов будет автоматически оборачивать имена таблиц. Второй параметр это алиас (синоним,  alias) таблицы. Если он не представлен, то будет использовать имя таблицы. Массив $options опциональный и идентичен массиву $options в статических запросах.

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

Еще один пример db_select()

Ниже еще один относительно простой запрос выборки из таблицы пользователей. Чуть позже мы разберем различные особенности запросов выборки и более сложные выборки с применением joins (объединения).

<?php
$query = db_select('users', 'u');

$query
  ->condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);

$result = $query->execute();
?>

 

Выше пример который эквивалентен следующему:

$result = db_query("SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0");

Это упрощенная форма запроса использованная на странице администрирования пользователей, которая может быть рассмотрена для дальнейшего изучения.

Joins (объединения, join)

Для объединения с другой таблицей используются join(), innerJoin(), leftJoin() или rightJoin() методы, такие как:

<?php
$table_alias = $query->join('user', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

В указанном примере мы добавили INNER JOIN (тип join добавляемый по умолчанию) с таблицей "user", которая задана с алиасом "u". Join имеет условие ON "n.uid=u.uid AND u.uid = :uid", где :uid имеет значение 5. Заметьте, что можно использовать заменители. Это позволяет добавлять переменные в структуру join в защищенном виде. Никогда не добавляйте переменные напрямую во фрагмент запроса, точно также как и в статические запросы (они могут быть причиной SQL-инъекций). innerJoin(), leftJoin() и rightJoin() методы оперируют одинаковыми для них соответствующими join-типами.

Возвращенное значение метода join это алиас таблицы к которому алиас привязан. Если этот алиас был уже определен для другой таблицы, что происходит редко, то Drupal присвоит другой алиас.

Заметьте, что место имени таблицы такого как "user" для всех join'ов в первом аргументе.

<?php
$myselect = db_select('mytable')
  ->fields('mytable')
  ->condition('myfield', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');
?>

Поля (Fields, addField)

Для добавления поля к запросу выбора используйте метод addField():

<?php
$title_field = $query->addField('n', 'title', 'my_title');
?>

Выше код, который описывает запрос выбора поля "title" из таблицы с алиасом "n" и присваивает полю алиас "my_title". Если алиас для поля не определен, то он будет сгенерирован автоматически. В большинстве случаев сгенерированный алиас будет просто именем поля. В этом примере автоматически он был бы назван "title". Если этот алиас уже существовал, то было бы использовано имя таблицы и имя поля. В этом примере это было бы "n_title". Если и этот алиас существует, то будет добавлен счетчик полей с таким именем, например "n_title_2".

Заметьте, если вы создаете запрос и не определяете алиас и стандартный алиас недоступен, значит у вас ошибка в коде. Если вы будете писать реализацию hook_query_alter() и вы не знаете точного имени алиаса, то стоит использовать стандартное имя алиаса.

Для выбора множества полей, просто вызывайте addField() несколько раз, в произвольном порядке (на то запрос и динамический). В большинстве случаев порядок полей не имеет значения, конечно если это не влияет на бизнес-логику модуля.

Вы можете использовать короткий вызов метода fields(), для добавления множества полей сразу.

<?php
$query->fields('n', array('nid', 'title', 'created', 'uid'));
?>

Выше описан вызов метода эквивалентный вызову addField() 4 раза, по разу для каждого поля. Однако, fields() не поддерживает описание алиасов для полей. Он также возвращает объект-запроса так, что все алиасы будут сгенерированы автоматически. Если вам нужно задавать алиасы, то лучше использовать addField() для доступа к БД.

Вызывая fields() без полей, мы вызываем аналог "SELECT *".

<?php
$query->fields('n');
?>

Здесь будет результат "n.*" включен в список полей запроса. Заметьте что не алиаса, который будет создан. Если таблица которая используется в SELECT * содержит поле, которое используется уже в другой таблице, это может вызвать коллизии. В этом случае результат будет содержать одно из полей с этим названием. Поэтому использовать SELECT * нежелательно.

DISTINCT

Некоторые SQL запросы могут дублировать записи в результате. В этом случае дублирующие записи могут быть отфильтрованы с помощью ключа "DISTINCT" в статическом запросе. В динамическом запросе используется метод distinct().

<?php
// Удаляем дублирующие записи из результата.
$query->distinct()
?>

Запомните что DISTINCT может вызвать большую нагрузку, так что не используйте его бесцельно или там где есть возможность избежать его использование.

Expressions (выражения, addExpression)

Конструктор запроса выборки поддерживает использование выражений (expressions) в списке полей. Пример выражений включает "двойное поле возраста", "подсчет всех имен" и подстроку заголовка поля. Нужно понимать, что много выражений могут использоваться в SQL функции и не все SQL функции стандартные для всех БД. Это зависит от модуля разработчика, который обеспечивает чтобы только все выражения были совместимы с БД.

Для добавления выражения в запрос используйте метод addExpression().

<?php
$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'uid_count', array(':offset' => 3600));
?>

В первой строке запроса будет добавлено в запрос "COUNT(uid) AS uid_count". Второй параметр это алиас для поля. В редких случаях алиас уже используется и будет создан новый, который будет возвращаться со значением addExpression(). Если алиас не определен, то будет создан новый, по умолчанию это "expression" (или expression_2, expression_3...).

 

Опциональный третий параметр это ассоциативный массив заполнителей значения которого используется как часть выражения. Запомните что некоторые выражение могут быть бесполезны если используется группировка Group By.

ORDER BY (Сортировка, orderBy)

Для добавления сортировки к динамическому запросу используйте метод orderBy().

<?php
$query->orderBy('title', 'DESC');
?>

В этом примере код будет посылать в запрос сортировку по полю title в убывающем порядке. Второй параметр может быть также ASC - возрастающий порядок. По умолчанию используется ASC - возрастающий. Запомните чтобы имя поля здесь должно быть задано через алиас, созданный методом addField() или addExpression(), так в большинстве случаев вы захотите использовать возвращаемое значение от этих методов, следите чтобы они совпадали. Для сортировки по нескольким полям, просто вызывайте orderBy() несколько раз и нужном порядке.

Случайный порядок (orderRandom)

 

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

Чтобы показать, что результат должен выводиться в случайном порядке вызовите метод orderRandom().

<?php
$query->orderRandom();
?>

Запомните что orderRandom() может работать совместно с orderBy(). Это возможно сделать как в следующем примере:

<?php
$query->orderBy('term')->orderRandom()->execute();
?>

В указанном примере сначала порядок задается через сортировку по полю term, а потом записи выводятся в случайном порядке.

GROUP BY (Группировка, groupBy)

Для группировки по полю используйте метод groupBy().

<?php
$query->groupBy('uid');
?>

Код в примере будет группировать результаты по полю uid. Заметьте что имя поля должно быть создано  и используется алиас. Как вы догадались нужно использовать метдо addField() или addExpression() чтобы заранее создать алиас. Группировку можно делать по нескольким полям, для этого вызывайте метод groupBy() несколько раз в нужном вам порядке.

Ranges и Limits (Диапазон и ограничения, range)

Запросы могут быть также ограничены по числу выводимых записей. В основном известны операторы "range". В MySQL это реализовано через параметр LIMIT. Для задания диапазона запроса используйте метод range().

<?php
$query->range(5, 10);
?>

Код выше будет выводить 10 записей начиная с 6-ой. С 6-ой потому что нумерация записей начинается с 0. Если вызвать range() повторно, то он перезапишет предыдущие значения. Если вызвать range() без параметров, то это ничего не изменить в запросе.

Сортировка таблицы (extend)

Производимый результат может быть отсортирован по столбцу, используя расширение TableSort и тогда будет добавлен заголовок к таблице. Заметьте что расширение возвращает новый объект-запрос, который будет использоваться.

<?php
$query = $query
  ->extend('TableSort')
  ->orderByHeader($header);
?>

Условия (condition)

Условия сложная тема и мы ее разберем после запросов выборки, обновления и удаления. Условия будут объяснены отдельно. Потому что для запросов обновления и удаления условия одинаковы, а вот для запросов выборки есть два типа условий: с использованием WHERE и с использованием HAVING. Поведение HAVING и WHERE почти одинаково. Для оператора WHERE используйте метод condition() или where(), для HAVING - havingConditon(), having().

Выполнение запроса (Execute)

Для создания запроса необходимо вызвать метод execute(), после чего запрос скомпилируется и будет отправлен к БД.

<?php
$result = $query->execute();
?>

Метод execute() вернет набор записей, который идентичен набору выводимого через db_query() и может быть обработан теми же путями.

<?php
$result = $query->execute();
foreach ($result as $record) {
  // Делаем что-нибудь с каждым $record
}
?>

• fetchField()
• fetchAllKeyed()
• fetchCol()

Эти методы требуют включения индексов к табличным алиасам. Однако, конструктор запросов не всегда гарантирует это.

Подсчет количества запросов

Любой запрос может вывести количество строк. Счетчик может вернуть количество строк оригинального запроса. Для получения подсчета используйте метод countQuery().

<?php
$count_query = $query->countQuery();
?>

$count_query это новый динамический запрос выборки без сортировки и когда он будет выполнен, вернет только одно значение - число записей, соответствующее оригинальному запросу. Так как PHP поддерживает методы сцепления для возвращаемых объектов, следующий код имеет общий вид:

<?php
$num_rows = $query->countQuery()->execute()->fetchField();
?>

Отладка

Для проверки SQL-запроса будет ли он сделан вызывайте метод __toString().

<?php
  print_r($query->__toString());
?>