Drupal работа с базой данных

1. Блог
2. Работа с базой данных в Drupal 8

Одним из нововведений Drupal 7 был объектно-ориентированный подход в системе построения запросов к базе данных. Работа системы строилась на таких функциях, как db_select, db_update, db_insert и т.д. Поддерживалось также и прямое написание запросов на основе функций db_query и db_query_range. Основные плюсы данного похода заключаются в отсутствии привязки к определённой СУБД и удобстве поддержки кода. В Drupal 8 все эти функции помечены, как deprecated (с выходом Drupal 9 будут удалены из ядра), и выполнение запросов к базе данных рекомендуется выполнять на основе системы соответствующих классов.

Общие принципы взаимодействия с базой данных в Drupal 8 строятся на получении объекта подключения к текущей базе данных и вызове методов данного объекта для построения конечного запроса и получения результата его выполнения. Получить объект подключения можно с помощью контейнера сервисов (класс Drupal), используя его статический метод database(). После получения объекта подключения нужно получить объект, соответствующий требуемому типу запроса. Как и в седьмой версии, в Drupal 8 построение запроса выполняется на основе таких методов, как fields(), join(), condition() и т. д. Выполнить запрос можно обращением к методу execute().

Выборка

Самый часто используемый способ "общения" с базой данных — это выборка. При разработке модулей не раз приходится сталкиваться с необходимостью получения данных из таблиц контрибных модулей или ядра Drupal. Получить объект SELECT-запроса можно с помощью метода select() из объекта подключения. Рассмотрим примеры основных SELECT-запросов:

1) Простейшая выборка с условием

В данном примере выполняется выбор значений полей uid и title таблицы node_field_data при условии, что значение в поле nid равно единице. Результат выполнения запроса формируется в виде массива, каждое значение которого будет объектом класса stdClass и будет содержать значения выбранных записей. Добавлять поля в запросе можно также с помощью метода addField(). По умолчанию в методе condition() используется оператор равенства, но можно использовать и другие операторы, передав оператор в качестве третьего параметра.

2) Выборка одного значения

В переменной $result будет содержаться прямое значение поля title. В случае наличия нескольких полей в результирующей записи, будет использовано первое из них. В случае наличия нескольких записей, будет использовано первое поле первой записи.

3) Выборка первой записи

В переменной $result будет содержаться первая запись результата выборки в виде ассоциативного массива. Для получения записи в виде объекта нужно использовать метод fetchObject().

4) Выборка первой колонки в виде простого массива

В переменной $result будет содержаться одномерный массив, содержащий значения поля title всех выбранных записей.

5) Объединение таблиц в выборке

Объединять таблицы в запросе также можно посредством методов innerJoin() (к слову метод join() — это синоним данного метода) и leftJoin().

6) Выбор определённого диапазона записей

С помощью метода range() можно управлять диапазоном выбираемых записей. Метод имеет два параметра: первый — позиция начала диапазона, второй — количество выбираемых записей с начала диапазона.

7) Использование условий ИЛИ в выборке

В переменной $result будут две записи — для ноды с nid 5 и для ноды с nid 7.

8) Подсчёт числа записей в выборке

9) Проверка значений на NULL

10) Применение сложных выражений в выборке

11) Группировка записей выборки

12) Применение сложных условий в запросе

Отмечу, что метод where() можно использовать не только в контексте SELECT-запросов. Например, его можно применить в запросе UPDATE или DELETE.

13) Сортировка выбранных записей

Направление сортировки можно задать посредством второго параметра метода orderBy(), который по умолчанию равен "ASC". Сделать рандомную сортировку можно на основе метода orderRandom().

Вставка и изменение

В случае, когда требуется добавление или изменение данных в определённой таблице, лучше обратиться к Drupal API (если таблица относится к ядру) или API контрибного модуля (если таблица относится к контрибному модулю). Однако, если вы создаёте собственный модуль, который оперирует своими таблицами, то операции добавления и изменения придётся описывать вручную.

Объект UPDATE-запроса получить можно с помощью метода update() из объекта подключения, а объект INSERT-запроса — с помощью метода insert(). Аналогично предыдущему разделу, рассмотрим основные примеры запросов:

1) Обновление записей

В результате выполнения этого кода в таблице example будут обновлены поля field_1, field_2 и field_3 (они получат значения $value_1, $value_2 и $value_3 соответственно) для записей, в которых поле field_4 равно $value_4.

2) Применение сложных выражений при обновлении

Если нужно применить сложное выражение для обновления данных, то следует использовать метод expression(). Он содержит три параметра — обновляемое поле, выражение, аргументы для выражения. В данном примере для всех записей численное поле field_1, будет увеличено на 100, а поле field_3 получит значения из field_2.

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

Стоит отметить, что метод fields() может принимать один или два параметра. Если первый параметр — ассоциативный массив, то ключи массива должны соответствовать полям таблицы, а значения — добавляемым значениям. В этом случае второй параметр опускается. Если первый параметр обычный массив, то он соответствует полям таблицы и нужно передать второй параметр, который должен содержать добавляемые значения в порядке, соответствующем порядку полей в первом параметре. Если второй параметр опущен, добавляемые значения нужно передать на основе метода values().

4) Добавление нескольких записей

5) Добавление или обновление в зависимости от наличия записи

Часть бывают случаи, что в зависимости от контекста нужно либо обновить запись, либо добавить новую. Реализация предварительного SELECT-запроса проверки наличия записи с дальнейшей условной конструкцией — это плохой тон. В Drupal 8 для этого имеется объект UPSERT-запроса (в Drupal 7 кстати такого типа запросов нет). Получить этот объект можно с помощью метода upsert(). Объект UPSERT-запроса обязательно должен содержать ключевое поле, по которому будет выполняться проверка существования записи. Поле должно быть уникальным в рамках таблицы. Добавить ключевое поле можно вызовом метода key().

Относительно обновления и добавления записей стоит ещё сказать, что метод execute() в случае операции обновления возвращает количество обновлённых записей, а в случае операции добавления — идентификатор добавленной записи, при условии, что добавлялась одна запись. Если добавлялось множество записей, возвращаемый идентификатор не определён.

Удаление

Иногда записи нужно и удалять. В случае таблиц модулей или ядра также следует использовать только соответствующее API. Ну, а для удаления данных из своих таблиц смело используйте объект DELETE-запроса, который, как вы, наверное, догадались, можно получить с помощью метода delete().

Построение условий для запроса удаления идентично построению для запросов выборки и изменения (можно использовать проверку на NULL, сложные выражения и т.д.), а метод execute() возвращает количество удалённых записей.

В виде заключения

В общем и целом структура построения запросов к базе данных в Drupal 8 не сильно изменилась относительно Drupal 7. Однако появились некоторые приятные и удобные вещи (например, запрос UPSERT). К слову, когда у вас не получается составить нужный запрос на основе объектной модели, вы всегда можете на свой страх и риск (если вы плохой SQL-щик) выполнить прямой запрос к базе данных с помощью метода query() объекта подключения. Метод в качестве входных параметров может принимать строку с запросом к базе данных.

Друпал предоставляет свои средства для доступа к базе данных. Это, во-первых, позволяет не зависеть от конкретного типа СУБД, а во-вторых, защититься от SQL инъекций. Самая первая функция, о которой следует узнать при работе с базой — db_query().

Начну, пожалуй, с примера, в стиле которого пишут почти все начинающие друпаллеры:

title, "node/<$row->nid>");
>
return theme(‘item_list’, $items);
?>

В этом примере сразу несколько вещей в корне неправильны.

Псевдонимы названий таблиц

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

Что нам это даст? Это обеспечит простоту обработки таблиц с префиксами. То есть, если у вас все таблицы в базе называются "pr_node", "pr_users" и т.д., Друпал автоматически будет подставлять корректные префиксы к таблицам, заключенным в скобки. Указание псевдонимов при этом избавит от надобности использовать фигурные скобки больше одного раза.

Фильтрация аргументов

Отсутствует фильтрация аргументов запроса. Это прямой путь к SQL инъекции. Если в $type окажется значение story ‘ UNION SELECT s.s > , то весь запрос будет уже таким:

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

Защититься от этого довольно просто, используя параметризацию запроса. При формировании запроса, Друпал использует синтаксис функции sprintf. В строке запроса вставляются заглушки, которые заменяются параметрами, которые идут отдельно. При этом параметры проходят проверку и экранирование, так что вы можете забыть об инъекциях, используя данный подход. Вот некоторые примеры:

%d", $nid);
db_query("SELECT n.n ", $type);
db_query("SELECT n.nid FROM n WHERE n.nid > %d AND n.type = ‘%s’", $nid, $type);
db_query("SELECT n.n AND n.nid > %d", $type, $nid);
?>

• %d — для целых чисел (integers)
• %f — для чисел с плавающей запятой, т.е. дробных (floats)
• %s — для строк (однако, обратите внимание, что в запросе, вокруг строки выставляются кавычки)
• %b — двоичные данные (не нужно оборачивать в кавычки)
• %% — заменяется на % (например, для LIKE % monkey % )

Для конструкций IN ( . , . , . ) , используйте функцию db_placeholders(), которая создаст нужную последовательность заменителей, по заданному массиву параметров, например:

db_query(‘SELECT * FROM n WHERE n.nid IN (‘. db_placeholders($nids) .’)’, $nids);
?>

Если вы используете модуль Devel, у вас есть очень простой способ получения конечных запросов для отладочных целей. Просто вызовите функцию db_queryd ( ) с точно такими же параметрами, как вы вызываете db_query ( ) .

Теперь, наш запрос будет выглядеть так:

Ранжирование результатов запроса

Наш пример на большом сайте выведет большущий список нодов. Что если нам можно ограничиться всего первым десятком? Первым позывом будет использовать SQL конструкцию LIMIT, например

и вроде бы все хорошо, но на Postgree SQL этот код приведет к ошибке, так как с этим сервером управления, вам нужно использовать конструкцию OFFSET 0 LIMIT 10 . А еще на каком-нибудь Оракле, синтаксис опять другой. Что же делать?

Ответ — использовать db_query_range() для лимитирования количества результатов запроса. Его использование аналогично db_query, за исключением того, что в после всех аргументов, вам нужно указать два параметра — номер первой строки, и количество результатов. Наш запрос преобразится в следующее:

И на последок, если вам ко всему еще нужен постраничный вывод, используйте функцию pager_query(). Она отличается от db_query_range ( ) наличием всего одного необязательного параметра, о котором вы можете почитать на странице документации. С этой функцией вывод листалки страниц прост как дважды два:

$output = theme(‘item_list’, $items);

// добавляем листалку
$output .= theme(‘pager’);

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

Возможность изменения запроса модулями

Довольно часто имеет смысл предоставить другим модулям возможность повлиять на ваш запрос. В Друпале это реализуется связкой функции db_rewrite_sql(), и реализациями хука hook_db_rewrite_sql() в модулях. Наш запрос будет выглядеть так:

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

$return[‘where’] = ‘u.login > ‘. time() — 60 * 60 * 24;
>
return $return;
break;
>
>
?>
Возвращенные из хука ‘join’ элементы, будут прикреплены к нашему запросу, ‘where’ — добавлены к списку условий, и наш запрос после обработки будет таким:

После этого, он, собственно, поступит в pager_query ( ) и будет обработан как обычно.

Финальный код примера

$output = theme(‘item_list’, $items);

Полезные ссылки

Статьи цикла «Безопасный код»

Для поддержки на Хабре (черт, в плюсе от главной, докиньте же голосов 🙂

• Drupal 6

• Блог
• Войдите или зарегистрируйтесь, чтобы отправлять комментарии

Комментарии

Постарался описать по максимуму обо всем часто используемом. Теперь можно смело тыкать нубов в ссылку 🙂

+1.
Александр, вы скоро наберёте материал на издание своей книги-)

Это точно. Очень просто и понятно все расписано. Сейчас же займусь пейджером для запросов.
Спасибище огромное!

Реально полезно и доступно написано, а главное акцент хороший выбрали — "безопасный код".

Если будете издавать книгу, то я первый в числе покупателей 🙂

Вплотную занимаемся обороной?

Хочу сказать пару слов по поводу пейджера.

Параметр $count_query должен в себе содержать отдельный запрос к базе, который даст общее количество строк результата запроса.

Здесь есть два аспекта. Если этот параметр не указывать, то это подходит только для простых запросов, которые не включают в себя множественные джоинты таблиц.
Но это всего лишь пол беды.
Вторая половина, это то, что создается дополнительный запрос к базе на предмет подсчета результата (я все оптимизацией болею)(нервно хихитает).
Где то я читал, что есть специальная команда, которая выводит общее количество строк результата запроса без сканирования таблиц. Сейчас найти ссылочку не могу. Чуть позже размещу эту инфу.

Да, я конечно же в курсе, просто не стал уводить слишком от первоначальной темы, кому надо, тот найдет уже сам 😉 А книга да, в планах есть. Готовтесь к семерке.

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

Вот сейчас у меня такая же проблема. Мучает меня этот двойной запрос при формировании списка с деятком джоинов и множеством условий тут http://rutalant.ru/talents. Не знал что возможно такое решение! Буду вникать.

Вячеслав, вы вероятней всего говорите об FOUND_ROWS()

Хотя, используя это, вы и получаете выигрыш в прорисовке первой странички, но в общем будет проигрыш, так как судя по нескольким комментам с друпал.орга, здесь не будет работать мускуловский кеш запросов (который бы закешировал нормальный COUNT запрос полсе первого раза). Кроме того, вы естественно, теряете в переносимости кода.

спасибо, как раз думал о том, что пора начинать пеуджинг использовать

хотелось бы еще увидеть подобную статью про программирование своих фильтров

хотелось бы еще увидеть подобную статью про программирование своих фильтров

Так зачем же ждать статью? Просто берите filter.module и препарируйте 🙂 Это более захватывающе чем статья, честное слово 🙂

Друпал, к сожалению, будет некорректно работать с функцией found_rows(), т.к. в [ru-api=pager_query]pager_query()[/ru-api] сначала выполняется $count_query запрос, а только потом — основной 🙁

zhylik, да, вы правы, не все так ажурно. Как бы там ни было, даже если сделать аналог pager_query() с нужной очередностью, мое замечание на счет производительности никуда не денеться.

Спасибо за отличную статью!

Безопасным код будет, только если правильно работать с базами данных. Друпал работает с базами неправильно, к сожалению — это и наследство старого MySQL, и некоторые другие факторы.
Вопрос: можно ли как -то работать с базами, указывая запрос в форме
SELECT n > Теория и практика говорит, что это самый правильный и безопасный способ, но может ли Drupal его поддерживать?

В смысле? Без алиасов?

Спасибо за полезный пост. А кстати — когда выйдет 7-ка ?

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

Далеко не всегда один запрос работает быстрее чем тот же поделенный на два простых.

Связано это чаще всего с тем каким образом mysql пытается оптимизировать запрос и что именно сейчас у него лежит в кеше.

Типичный пример
SELECT COUNT(*) FROM message WHERE u > и
SELECT COUNT(u >

Первый запрос работает в разы быстрее чем второй. Потому как для работы первого достаточно пройтись по индексу uid и посчитать кол во записей. А индексы у нас упорядочены и в большинстве случаев лежат в кеше.

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

Второй аспект,
Формат таблиц myisam

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

И так далее. Так что еще раз повторяю, Один запрос далеко не всегда быстрее чем даже 20 более простых.

Demimurych, наверное, имелось в виду, что 2-ой запрос быстрее? 🙂

В Drupal 7 очень удобно сделано получение результатов выборки из базы данных. В этой статье я приведу примеры функций, позволяющих получить результат в удобном виде.

Для воспроизведения этих примеров, я установил на своей локальной машине "чистый" Drupal 7, добавил 5 нод типа "Article" и написал свой модуль, в котором мы и будем получать из базы эти самые ноды.

Запрос к БД будет предельно простым — получение всех нод из базы:

Вывод результатов выборки я буду осуществлять с помощью php функции var_dump() .

Получение нескольких записей из БД

fetchAll()

Этот метод преобразует результат выборки в ассоциативный массив объектов, где полями объекта являются выбранные поля. Например запрос:

вернёт нам следующий результат:

fetchAllAssoc(key)

Метод fetchAllAssoc(key) позволяет, аналогично методу fetchAll() , получить выборку в виде ассоциативного массива объектов, но ключами этого массива мы можем управлять с помощью переменной key .

Например, следующий запрос вернёт ассоциативный массив со всеми нодами, где ключами массива являются nid соотвествующих нод:

fetchAllKeyed()

Достаточно интересный метод, позволяет получить только 2 поля в виде ассоциативного массива. Т.е. если в предыдущих запросах мы могли получить массив сразу всех полей нод (например nid, title, status), то этот метод позволяет получить только 2 поля, например nid и title нод:

В запросе выше я специально добавил еще поле status в результат, чтобы можно было понять работу метода fetchAllKeyed():

Как видите, метод fetchAllKeyed() вернул ассоциативный массив, в котором ключами являются nid нод, а значениями title нод. Третьего поля status нет, хотя оно и присутствует в выборке, потому что метод работает только с первыми двумя полями (в нашем случае nid и title ).

fetchAllKeyed(field1,field2)

Есть еще один вариант применения метода fetchAllKeyed(field1, field2) , передавая ему аргументы. Переменные field1 и field2 определяют поля, которые будут участвовать в построении ассоциативного массива. Т.е. если в предыдущем случае, вызвав метод fetchAllKeyed() без передачи аргументов, мы получали ассоциативный массив, где участвовали только 2 первых поля в выборке. Теперь, с указанием конкретных полей мы можем указывать, что поле filed1 будет ключами ассоциативного массива, в поле field2 — значениями.

Например следующий запрос вернёт ассоциативный массив всех нод, где ключами являются nid нод, а значениями — время создания этих нод:

fetchCol(field)

Метод fetchCol позволяет получить результат выборки в виде ассоциативного массива, где значениями являются указанный в field порядковый номер поля выборки. Если field не указан, то по умолчанию будут выведены результаты первого поля. Например:

Т.е. мы получили массив всех nid нод в БД, т.к. в выборке первым полем является nid ->fields(‘n’, array(‘nid’, ‘title’)) , а поле title не участвует в результате.

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

rowCount()

Метод rowCount() будет полезен, если необходимо получить количество записей в выборке (не в таблице, а именно в выборке). Например, получим количество всех опубликованных материалов:

Получение одной записи из БД

fetchAssoc()

Метод fetchAssoc() позволяет получать результат выборки в виде ассоциативного массива, где ключами являются поля выбираемой записи, а значениями — значения этой записи. Например:

Результат выполнения запроса:

fetchObject()

Метод fetchObject() аналогичен методу fetchAssoc() , только вместо ассоциативного массива он получает объект:

fetchColumn(key)

Метод fetchColumn(key) получает значение указанного (если key не указан, то первого) поля записи в выборке.

Запрос вернёт значение nid ноды с n >

Теперь изменим немного запрос и укажет индекс поля, которое нужно вывести, например заголовок ноды с n >

Этот запрос куда более полезен. Довольно часто нужно получить заголовок ноды, зная её id.

Конечно, лучше получать поля ноды с помощью функции node_load(nid) в API Друпала. Но проблема загрузки всей ноды по id заключается в том, что функция грузит все поля ноды, что плохо сказывается на производительности. Так что если необходимо быстро получить какое нибудь поле ноды, лучше воспользоваться прямым запросом к БД.