Если вам, как разработчику, приходилось создавать в базе данных свою таблицу для хранения данных, то вы знаете, как важно написание своего функционала для работы с этой таблицей. Обязательно требуется написать функции для:
- добавление данных,
- их обновление или изменение,
- удаление,
- а также их получение.
Из всего этого списка, именно получение данных из таблицы может вызвать наибольшие проблемы у разработчика, потому что данные можно получать по множеству критериев и важно получить удобный инструмент для получения данных по этим критериям не только для себя, но и предложить его другим разработчикам, которые будут работать с вашей таблицей. Чем больше таблица имеет столбцов, тем больше комбинаций входящих параметров на выборку данных она может принимать, но не у всех хватает времени и возможностей, чтобы учесть все эти параметры при написании функции на выборку. Как правило, ограничиваются выборкой, как им кажется, наиболее важных данных, игнорируя другие, а параметры выборки могут быть жестко ограничены, что снижает возможности для работы с данными таблицы.
Опыт написания плагинов или дополнений привел меня к необходимости писать под каждую новую таблицу в базе данных свой класс, который позволял бы строить сложные запросы для получения данных. На основе таких классов было очень удобно создавать шорткоды, которые бы выводили определенные данные в зависимости от полученных атрибутов, но еще удобнее было взаимодействовать с классом через php-код, формируя массив определенных параметров и сразу получая нужные данные, без необходимости работы напрямую с классом $wpdb.
Со временем подобных классов стало достаточно много (один для каждой таблицы) и так как суть у них была одна, то решено было написать универсальное решение, которое позволить работать со всеми кастомными таблицами плагина и дополнений из одного места.
Шестнадцатая версия плагина WP-Recall, кроме всего прочего, получила в ядро новый класс Rcl_Query, который позволил перевести почти все запросы к БД на получение данных на себя, сформировав, таким образом, рекомендуемый стандарт, которым очень удобно пользоваться. Рассмотрим подробнее порядок работы с новым классом и прикинем его возможности для применения в наших будущих разработках к плагину WP-Recall.
Внимание! Для того, чтобы читать и понимать суть написанного ниже, вы должны иметь представление о работе с классом wpdb и уметь строить SQL-запросы к базе данных.
Для начала рассмотрим базовый пример работы с данным классом напрямую, а затем перейдем к более удобному варианту.
Для примера работы будем рассматривать не абы какую, а вполне настоящую таблицу, которая использует в дополнении групп, со следующей структурой:
$table = RCL_PREF ."groups";
$sql = "CREATE TABLE IF NOT EXISTS ". $table . " (
ID INT(20) NOT NULL,
admin_id INT(20) NOT NULL,
group_users INT(20) NOT NULL,
group_status VARCHAR(20) NOT NULL,
group_date DATETIME NOT NULL,
PRIMARY KEY id (id),
KEY admin_id (admin_id)
) $collate;";
Чтобы начать получать данные из этой таблицы через класс Rcl_Query необходимо сообщить классу структуру этой таблицы. Делается это следующим образом:
$table = array( 'name' => RCL_PREF ."groups", 'as' => 'rcl_groups', 'cols' => array( 'ID', 'admin_id', 'group_users', 'group_status', 'group_date' ) ); $query = new Rcl_Query($table);
Как видно из кода выше, мы сформировали массив с данными таблицы, указали ее имя, псевдоним и перечислили наименование всех столбцов с которыми будем в дальнейшем работать и передали этот массив в класс Rcl_Query. В результате мы получили объект $query, который и позволит нам получать данные из нашей таблицы согласно переданных параметров.
Мы помним, что для построения запросов к БД через класс $wpdb мы могли использовать методы: get_var, get_row, get_col и get_results. При работе с классом Rcl_Query эти методы также можно и нужно использовать, только передавать в них мы будем не строку запроса, а массив.
Итак давайте рассмотрим несколько простых примеров.
... WHERE 'a' = 'b' ...
Для построения несложных запросов в которых нужно получить данные по значению определенного столбца строим массив в котором указываем наименование столбца и указываем нужное значение для него:
1. Получим данные группы с идентификатором 10:
$args = array(
'ID' => 76
);
$query->get_row($args);
В качестве результата мы получим объект с данными группы идентификатор, которой равен 76. Причем будут выбраны данные всех столбцов найденной строки, примерно со следующей структурой:
stdClass Object
(
[ID] => 76
[admin_id] => 1
[group_users] => 105
[group_status] => closed
[group_date] => 2015-11-15 18:24:18
)
Для указания условий выборки можно использовать наименования всех зарегистрированных столбцов таблицы, например:
$args = array(
'admin_id' => 10,
'group_status' => 'open'
);
$query->get_row($args);
SELECT 'a', 'b' ...
Если необходимо выбрать значения только из определенных столбцов таблицы, то используем их перечисление внутри массива 'fields'.
2. Для того, чтобы получить только значения admin_id и group_status, построим следующий запрос:
$args = array(
'ID' => 76,
'fields' => array(
'admin_id',
'group_status'
)
);
$result = $query->get_row($args);
и тогда мы действительно получим объект только с указанными данными:
stdClass Object
(
[admin_id] => 1
[group_status] => closed
)
3. Если мы хотим получить значение только одного столбца, то имеет смысл использовать метод get_var:
$args = array(
'ID' => 76,
'fields' => array(
'admin_id'
)
);
$result = $query->get_var($args);
тогда мы получим только идентификатор администратора группы, в данном случае, "1".
Перейдем к более сложным и интересным запросам с методом get_results.
... WHERE 'a' = 'b' AND 'c' = 'd' ...
4. Получим данные групп, в которых admin_id равен 1, а group_status - 'closed', т.е. закрытые группы пользователя с ID 1:
$args = array(
'admin_id' => 1,
'group_status' => 'closed'
);
$result = $query->get_results($args);
В результате получим вполне ожидаемый массив объектов:
Array
(
[0] => stdClass Object
(
[ID] => 656
[admin_id] => 1
[group_users] => 4
[group_status] => closed
[group_date] => 2016-04-06 13:40:57
)
[1] => stdClass Object
(
[ID] => 605
[admin_id] => 1
[group_users] => 8
[group_status] => closed
[group_date] => 2016-03-09 14:29:23
)
[2] => stdClass Object
(
[ID] => 76
[admin_id] => 1
[group_users] => 105
[group_status] => closed
[group_date] => 2015-11-15 18:24:18
)
)
... WHERE 'a' >= 'b' AND 'c' <= 'd' ...
5. Чтобы получить данные со значения от указанного или со значениям до указанного включительно, необходимо при указании наименования колонки использовать постфиксы "__from" и "__to". Например, выберем группы в которых количество участников более 100, но менее 200:
$args = array(
'group_users__from' => 100, //от 100
'group_users__to' => 200 //до 200
);
$result = $query->get_results($args);
... WHERE 'a' LIKE '%b%' ...
5. Для выборки данных через указание паттерна LIKE необходимо указывать постфикс "__like":
$args = array(
'group_name__like' => 'string'
);
$result = $query->get_results($args);
... ORDER BY 'a' ORDER ...
Для сортировки по значениям определенного столбца таблицы используем указание параметра 'orderby' указав в качестве значения наименования нужного столбца таблицы.
6. Усложним запрос, получив только значения указанных столбцов и отсортировав по кол-ву пользователей в группе по убыванию:
$args = array(
'admin_id' => 1,
'group_status' => 'closed',
'fields' => array(
'ID',
'group_users',
),
'orderby' => 'group_users',
'order' => 'DESC'
);
$result = $query->get_results($args);
Результат данного запроса:
Array
(
[0] => stdClass Object
(
[ID] => 76
[group_users] => 105
)
[1] => stdClass Object
(
[ID] => 605
[group_users] => 8
)
[2] => stdClass Object
(
[ID] => 656
[group_users] => 4
)
)
... LIMIT a,b ...
Для пропуска определенного количества найденных первых значений из выборки используется параметр 'offset' с указанным значением количества пропускаемых строк. Параметр 'number' указывает максимальное количество найденных строк в выборке.
7. Дополним запрос, указав к выборке только 3 группы, пропустив первые 5:
$args = array(
'admin_id' => 1,
'group_status' => 'closed',
'fields' => array(
'ID',
'group_users',
),
'orderby' => 'group_users',
'order' => 'DESC',
'offset' => 5,
'number' => 3
);
$result = $query->get_results($args);
если для параметра number указать значение "-1", то будут выбраны все записи без ограничений
... 'a' IN (b,c) ...
Если необходимо выбрать строки с определенными значениями указанных столбцов, то используется указание параметра с именем построенным по шаблону: 'colname__in', а в качестве значения указывается массив значений столбца к выборке. Например:
'admin_id__in' => array(1,44)
'ID__in' => array(76,35,10)
'group_users__in' => array(100,150,200)
8. Получим все группы, в которых администраторами являются пользователи с ID равным 1 и 44, отсортируем результат по идентификатору группы:
$args = array(
'admin_id__in' => array(1,44),
'fields' => array(
'ID',
'admin_id',
'group_users',
),
'orderby' => 'ID',
'order' => 'DESC'
);
$result = $query->get_results($args);
... 'a' NOT IN (b,c) ...
Для исключения из выборки найденных строк по определенным параметрам используется указанием параметра, имя которого строится на основании имени столбца по шаблону: 'colname__not_in', а в качестве значения указывается массив со нужными значениями.
'admin_id__not_in' => array(1,44)
'ID__not_in' => array(76,35,10)
'group_users__not_in' => array(100,150,200)
9. Получим 10 групп, пропустив первые 5 и исключив из выборки группы с идентификаторами 1, 5 и 23:
$args = array(
'ID__not_in' => array(1, 5, 23),
'number' => 10,
'offset' => 5
);
$result = $query->get_results($args);
SELECT COUNT('a') ...
10. Посчитаем все открытые группы сайта:
$args = array(
'group_status' => 'open'
);
$result = $query->count($args);
В метод 'count', можно передать любой массив из примеров выше и результатом будет подсчет количества данных выборки, согласно переданных параметров.
DATE_QUERY
11. Выберем все открытые группы, созданные 20 апреля 2016 года:
$args = array(
'group_status' => 'open',
'date_query' => array(
'column' => 'group_date',
'year' => '2016',
'month' => '04',
'day' => '20'
)
);
$result = $query->get_results($args);
12. Выберем все группы, созданные за январь и февраль 2017 года:
$args = array(
'date_query' => array(
'column' => 'group_date',
'value' => array(
'2017-01',
'2017-02'
),
'compare' => 'BETWEEN'
)
);
$result = $query->get_results($args);
13. Выберем группы, созданные с 10 января 2017 года по 25 января 2017 года:
$args = array(
'date_query' => array(
'column' => 'group_date',
'value' => array(
'2017-01-10',
'2017-01-25'
),
'compare' => 'BETWEEN'
)
);
$result = $query->get_results($args);
Регистрация дочернего класса для таблицы
Примеры выше показывают основы работы с классом Rcl_Query напрямую, но на практике подобный подход не удобен, тк придется постоянно формировать массив с данными таблицы и передавать его в класс.
Для того, чтобы упростить процесс работы со своей таблицей имеет смысл зарегистрировать дочерний класс, который и будет задавать данные нужной таблицы:
class Rcl_Groups_Query extends Rcl_Query {
function __construct() {
$table = array(
'name' => RCL_PREF ."groups",
'as' => 'rcl_groups',
'cols' => array(
'ID',
'admin_id',
'group_users',
'group_status',
'group_date'
)
);
parent::__construct($table);
}
}
Теперь у нас есть свой класс, с которым мы и будем работать когда потребуется получить данные из нашей таблицы. Например так:
$query = new Rcl_Groups_Query();
$args = array(
'admin_id' => 1,
'group_status' => 'closed'
);
$result = $query->get_results($args);
Оборачиваем дочерний класс в функцию
Возможно, для получения данных из таблицы другим разработчикам будет удобнее работать с функцией, чем с классом, поэтому мы можем обернуть свой дочерний класс в функцию :
function get_groups($args){
$query = new Rcl_Groups_Query();
return $query->get_results($args);
}
В результате мы получим удобную функцию, которая сможет принимать массив всех параметров, что мы рассматривали выше и будет отдавать данные согласно переданных параметров. Останется только задокументировать ее и предложить для работы другим разработчикам. Пример работы:
$groups = get_groups(
array(
'admin_id' => 1,
'group_status' => 'closed',
'fields' => array(
'ID',
'group_users',
),
'orderby' => 'group_users',
'order' => 'DESC',
'offset' => 5,
'number' => 3
)
);
Мы рассмотрели порядок работы с классом Rcl_Query на примере одной таблицы. Подобным образом, буквально за минуту, можно подключать к классу любую свою таблицу и сразу после этого начинать легко строить запросы и получать нужные данные.
JOIN. Работа с несколькими таблицами
Для того, чтобы удобно работать с несколькими таблицами, то лучше всего зарегистрировать для данных таблиц дочерние классы с данными этих таблиц, чтобы затем быстро подставлять их в массив запроса, но, если такой необходимости или возможности нет, то можно передавать данные таблиц прямо в массив. Мы рассмотрим вариант работы с двумя заранее зарегистрированными query-классами двух таблиц.
Чтобы сформировать запрос ко второй таблице, необходимо дополнить массив запроса массивом 'join_query', где будут размещаться данные запроса ко второй таблице. Данный массив должен содержать некоторые обязательные элементы:
'join_query' => array(
array(
'table' => array(), //данные таблицы, обязательно
'on_*' => 'col_name', //параметры объединения по ON, обязательно,
'join' => 'INNER', //тип объединения, необязательно
),
array(
...
),
...
);
'table' - массив с данными таблицы, со структурой, которую мы рассматривали выше при регистрации первой таблицы,
'on_*' - параметр запроса ON, дополняется наименованием колонки из первой таблицы, а значением является имя колонки из второй таблицы, например, запись вида: 'on_ID' => 'user_id' для таблиц wp_users и wp_usermeta, будет интерпретироваться как 'ON wp_users.ID=wp_usermeta.user_id',
'join' - может быть LEFT, RIGHT, INNER и тд., по-умолчанию - INNER.
Вторая таблица будет иметь следующую структуру:
$table = RCL_PREF ."groups_users";
$sql = "CREATE TABLE IF NOT EXISTS ". $table . " (
ID bigint (20) NOT NULL AUTO_INCREMENT,
group_id INT(20) NOT NULL,
user_id INT(20) NOT NULL,
user_role VARCHAR(20) NOT NULL,
status_time INT(20) NOT NULL,
user_date DATETIME NOT NULL";
Допустим мы уже зарегистрировали для этой таблицы свой query-класс Rcl_Groups_Users_Query, поэтому можем переходить к построению запроса. Дополним пример №4 запросом ко второй таблице и получим данные пользователей выбранных из первой таблицы групп:
//получим объект для выборки групп
$Groups = new Rcl_Groups_Query();
//получим объект для выборки членов групп
$Users = new Rcl_Groups_Users_Query();
$args = array(
'admin_id' => 1,
'group_status' => 'closed',
'join_query' => array(
array(
'join' => 'LEFT',
'table' => $Users->query['table'],
'on_ID' => 'group_id',
)
)
);
$result = $Groups->get_results($args);
Переданный массив сформирует следующий запрос:
SELECT
rcl_groups.*,
rcl_groups_users.*
FROM wp_rcl_groups AS rcl_groups
LEFT JOIN wp_rcl_groups_users AS rcl_groups_users
ON rcl_groups.ID = rcl_groups_users.group_id
WHERE
rcl_groups.admin_id = '1'
AND rcl_groups.group_status = 'closed'
ORDER BY rcl_groups.ID DESC LIMIT 0,30
Для уточнения запроса, мы также можем дополнять массив 'join_query' параметрами, которые рассматривались в примерах выше. Например, дополним запрос указав для выборки группы куда входит пользователь с ID 44, а выбирать из первой и второй таблицы будем только поля указанные в параметрах 'fields':
$Groups = new Rcl_Groups_Query();
$Users = new Rcl_Groups_Users_Query();
$args = array(
'admin_id' => 1,
'group_status' => 'closed',
'fields' => array(
'ID',
'group_status'
),
'join_query' => array(
array(
'join' => 'LEFT',
'table' => $Users->query['table'],
'on_ID' => 'group_id',
'user_id' => 44,
'fields' => array(
'user_id',
'user_role'
)
)
)
);
$result = $Groups->get_results($args);
В результате будет сформирован более точный запрос:
SELECT
rcl_groups.ID,
rcl_groups.group_status,
rcl_groups_users.user_id,
rcl_groups_users.user_role
FROM wp_rcl_groups AS rcl_groups
LEFT JOIN wp_rcl_groups_users AS rcl_groups_users
ON rcl_groups.ID = rcl_groups_users.group_id
WHERE
rcl_groups.admin_id = '1'
AND rcl_groups.group_status = 'closed'
AND rcl_groups_users.user_id = '44'
ORDER BY rcl_groups.ID DESC
LIMIT 0,30
Таким образом, мы можем легко работать с двумя, тремя и большим количеством таблиц.
Конечно, в первую очередь, класс Rcl_Query стоит рассматривать как решение для быстрого построения несложных запросов, но как показывает практика именно такие и составляют 90% от всех запросов, которые приходится писать под очередной плагин или дополнение. Класс будет постепенно развиваться, дополняться новыми возможностями, что будет автоматически расширять возможности для выборки данных для всех таблиц подключенных к классу, что очень удобно.
Некоторые разработчики могут усомниться в необходимости данного класса, дескать, нам и с wpdb неплохо живется. Спорить не буду, инструменты должны использоваться с учетом своей целесообразности, если кому то проще быстро написать простой запрос через wpdb, то это вполне оправдано, но когда речь идет о построении гибкого функционала и возникает необходимость получения данных на основе непостоянного множества параметров, то класс Rcl_Query будет просто незаменим.
Только лишь создав таблицу в базе данных и подключив ее к классу Rcl_Query разработчик сразу получает возможность работать с данными этой таблицы и получать данные по множеству входящих параметров!
Класс Rcl_Query - гибкий и удобный, а самое главное, несложный инструмент для работы, сохраняющий время разработчика. Буду рад, если он кого то заинтересует и будет применяться в вашей работе.
Автор публикации
Можно ли как то сортировать результат так же как он передан в IN()?
В простом sql запросе это делается через ORDER BY FIELD(ID,5,6,2,1,7)
нет, такого сделать нельзя
Можно ли сделать сортировку по нескольким полям?
специально в классе это не реализовывал, тк пока не было необходимости это применить, но думаю, можно просто указать нужные поля в параметре orderby правда через костыль:
'orderby' => 'colName1,tableAs.colName2',
т.е. для первого столбца tableAs не указывается, тк он подставляет автоматически при построении запроса