Построитель запросов QueryBuilder
QueryBuilder реализует Database Abstraction Layer (DBAL) для следующий операторов: Select, Insert, Update, Delete, Rename, Drop, Truncate, Lock.
Использование QueryBuilder возможно как отдельно для выполнения соответствующих запросов, так и совместно с ORM.
Совместная работа ORM и QueryBuilder
Объект ORM имеет метод ->queryBuilder(), через который можно добавить дополнительные условия выборки у объекта или связи.
$oBook = Core_Entity::factory('Book');
$oBook->queryBuilder()
->where('value', '=', 99);
$aBooks = $oBook->findAll();
Дополнительные условия для связи.
$oComments = Core_Entity::factory('Book')->Comments;
$oComments->queryBuilder()
->where('active', '=', 1);
$aComments = $oComments->findAll();
Примеры работы
Получение данных в виде массива или объектов
Для получения данных в виде массива с массивами, укажите asAssoc(), для получения массива объектов задайте asObject(), если требуется заполнение данными объектов определенного класса, укажите название класс asObject('className').
Получение массива всех найденных элементов
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()
->from('structures')
->where('column1', '=', 1);
$aRows = $oCore_QueryBuilder_Select->execute()->asAssoc()->result();
Получение одного найденного элемента
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()
->from('structures')
->where('column1', '=', 1)
->limit(1);
$row = $oCore_QueryBuilder_Select->execute()->asAssoc()->current();
Указание выбираемых столбцов
Вариант 1:
$oCore_QueryBuilder_Select = Core_QueryBuilder::select('id', 'name', 'description')
->from('structures');
Вариант 2:
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()
->select('id')
->select('name')
->select('description')
->from('structures');
Использование псевдонимов
Вариант 1:
$oCore_QueryBuilder_Select = Core_QueryBuilder::select('id', array('name', 'newname'), array('description', 'newdescription'))
->from('structures');
Вариант 2:
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()
->select('id')
// `name` AS `newname`
->select(array('name', 'newname'))
// `description` AS `newdescription`
->select(array('description', 'newdescription'))
->from('structures');
При использовании объединений, для таблицы также может быть использован псевдоним:
$Core_QueryBuilder_Select->leftJoin(array('jointable', 'join1'), 'join_field', '=', 'join1.field2');
Скалярный подзапрос
http://dev.mysql.com/doc/refman/5.7/en/scalar-subqueries.html
// SELECT (SELECT MAX(`column2`) FROM `t2`) FROM `t1`
$oCore_QueryBuilder_Select2 = Core_QueryBuilder::select('MAX(column2)')->from('t2');
$oCore_QueryBuilder_Select = Core_QueryBuilder::select($oCore_QueryBuilder_Select2)->from('t1');
Сравнение с использованием подзапроса
http://dev.mysql.com/doc/refman/5.7/en/comparisons-using-subqueries.html
// SELECT FROM `t1` WHERE `column1` = (SELECT MAX(`column2`) FROM `t2`)
$oCore_QueryBuilder_Select2 = Core_QueryBuilder::select('MAX(column2)')->from('t2');
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()->from('t1')
->where('column1', '=', $oCore_QueryBuilder_Select2);
INNER JOIN
Полное объединение в случае, если в обеих таблицах столбец имеет одинаковое наименование.
// INNER JOIN `join1` USING (`join_field`)
$Core_QueryBuilder_Select->join('join1', 'join_field');
Полное объединение в случае, если в таблицах столбцы имеют различные наименования.
// INNER JOIN `jointable` ON `join_field` = `jointable`.`join_field2`
$Core_QueryBuilder_Select->join('jointable', 'join_field1', '=', 'jointable.join_field2');
Полное объединение с дополнительными условиями.
// INNER JOIN `jointable` ON `join_field` = `join_field2` AND `A` = '123' AND `B` LIKE 'xyz'
$Core_QueryBuilder_Select->join('jointable', 'join_field1', '=', 'join_field2', array(
array('AND' => array('A', '=', '123')),
array('AND' => array('B', 'LIKE', 'xyz'))
));
Полное объединение с расширенным "сырым" условием объединения * с версии 7.1.5
$Core_QueryBuilder_Select->join('shop_groups', Core_QueryBuilder::raw(' здесь сырое условие в ON '));
Объединение с подзапросом и алиасом для поздапроса:
$oSubQuery = Core_QueryBuilder::select(
'entity_id',
array(Core_QueryBuilder::expression('DATE(datetime)'), 'event_date'),
array(Core_QueryBuilder::expression('MIN(entity_event_id)'), 'min_entity_event_id')
)
->from('second_table')
->where('deleted', '=', 0)
->where('active', '=', 1)
->where('datetime', '>=', '2026-02-01 00:00:00')
->where('datetime', '<=', '2026-02-28 23:59:59')
->groupBy('user_id')
->groupBy('event_date');
$oMainQuery = Core_QueryBuilder::select('*')
->from('entities')
->join(
array($oSubQuery, 'daily_min'), 'daily_min.entity_id', '=', 'entities.id'
);
LEFT OUTER JOIN
Левое внешнее объединение в случае, если в обеих таблицах столбец имеет одинаковое наименование.
// LEFT OUTER JOIN `join1` USING (`join_field2`)
$Core_QueryBuilder_Select->leftJoin('join1', 'join_field2');
Левое внешнее объединение в случае, если в таблицах столбцы имеют различные наименования.
// LEFT OUTER JOIN `jointable` ON `join_field` = `join_field2`
$Core_QueryBuilder_Select->leftJoin('jointable', 'join_field', '=', 'join_field2');
RIGHT OUTER JOIN
Правое внешнее объединение в случае, если в обеих таблицах столбец имеет одинаковое наименование.
// RIGHT OUTER JOIN `join1` USING (`join_field2`)
$Core_QueryBuilder_Select->rightJoin('join1', 'join_field2');
Правое внешнее объединение в случае, если в таблицах столбцы имеют различные наименования.
// RIGHT OUTER JOIN `jointable` ON `join_field` = `jointable`.`join_field2`
$Core_QueryBuilder_Select->rightJoin('jointable', 'join_field', '=', 'jointable.join_field2');
WHERE
Задает дополнительные условия выборки, используются методы where(), orWhere() и whereRaw().
Допустимые условия:
- '<=>' - NULL-safe equal to operator
- '=' - Equal operator
- '>=' - Greater than or equal operator
- '>' - Greater than operator
- '<=' - Less than or equal operator
- '<' - Less than operator
- 'LIKE' - Simple pattern matching
- 'NOT LIKE' - Negation of simple pattern matching
- '!=' - Not equal operator
- '<>' - Not equal operator
- 'REGEXP' - Whether string matches regular expression
- 'NOT REGEXP' - Negation of REGEXP
- 'RLIKE' - Whether string matches regular expression
- 'NOT RLIKE' - Negation of RLIKE
- 'IN' - Check whether a value is within a set of values
- 'NOT IN' - Check whether a value is not within a set of values
- 'COALESCE' - Return the first non-NULL argument
- 'GREATEST' - Return the largest argument
- 'INTERVAL' - Return the index of the argument that is less than the first argument
- 'LEAST' - Return the smallest argument
- 'STRCMP' - Compare two strings
- 'BETWEEN' - BETWEEN ... AND ... Check whether a value is within a range of values
- 'NOT BETWEEN' - NOT BETWEEN ... AND ... Check whether a value is not within a range of values
- 'IS' - Test a value against a boolean
- 'IS NOT' - Test a value against a boolean
// WHERE `a1` > '2'
$Core_QueryBuilder_Select->where('a1', '>', '2');
Несколько условий, вызванных подряд, будут соединяться с использованием AND, если вы хотите указать OR, то можно между методами вызвать setOr() или использовать метод ->orWhere():
// WHERE `a1` > 2 OR `a2` < 100
$Core_QueryBuilder_Select->where('a1', '>', 2)->setOr()->where('a2', '<', 100);
// вариант 2
$Core_QueryBuilder_Select->where('a1', '>', 2)->orWhere('a2', '<', 100);
Фильтрация по совпадению с массивом значений реализуется методом where(), а также методами whereIn(), orWhereIn(), whereNotIn() и orWhereNotIn(). Обратите внимание, в примере массиве содержит как значение NULL, так и строку 'NULL'.
// WHERE `a1` IN (17, 19, NULL, 'NULL')
$Core_QueryBuilder_Select->where('a1', 'IN', array(17,19, NULL, 'NULL'));
// вариант 2
$Core_QueryBuilder_Select->whereIn('a1', array(17,19, NULL, 'NULL'));
// WHERE `a2` NOT IN (17, 19)
$Core_QueryBuilder_Select->where('a2', 'NOT IN', array(17,19));
// вариант 2
$Core_QueryBuilder_Select->whereNotIn('a2', array(17,19));
// WHERE `a3` IN (17, 19) OR `a4` NOT IN (13, 14)
$Core_QueryBuilder_Select->where('a3', 'IN', array(17,19))->setOr()->where('a4', 'NOT IN', array(17,19));
// вариант 2
$Core_QueryBuilder_Select->whereIn('a3', array(17,19))->setOr()->whereNotIn('a4', array(17,19));
// вариант 3
$Core_QueryBuilder_Select->whereIn('a3', array(17,19))->orWhereNotIn('a4', array(17,19));
Фильтрация по интервалу значений возможна как с использованием where(), так и методов whereBetween(), orWhereBetween(), whereNotBetween() и orWhereNotBetween()
// WHERE `a1` BETWEEN 1 AND 10
$Core_QueryBuilder_Select->where('a1', 'BETWEEN', array(1, 10));
// вариант 2
$Core_QueryBuilder_Select->whereBetween('a1', 1, 10);
// WHERE `a2` BETWEEN 1 AND 10 AND `a3` NOT BETWEEN 100 AND 200
$Core_QueryBuilder_Select->where('a2', 'BETWEEN', array(1, 10))->where('a3', 'NOT BETWEEN', array(100, 200));
// вариант 2
$Core_QueryBuilder_Select->whereBetween('a2', 1, 10)->whereNotBetween('a3', 100, 200);
Проверка поля на NULL возможна как с использованием where(), так и методами whereIsNull(), orWhereIsNull(), whereIsNotNull() и orWhereIsNotNull(), например
// WHERE `a1` IS NULL
$Core_QueryBuilder_Select->where('a1', 'IS', NULL);
// вариант 2
$Core_QueryBuilder_Select->whereIsNull('a1');
// WHERE `a1` IS NULL OR `a2` IS NULL
$Core_QueryBuilder_Select->whereIsNull('a1')->orWhereIsNull('a2');
Сырое задание условия WHERE осуществляется методом whereRaw(), например
// WHERE `a1` > 2
$Core_QueryBuilder_Select->whereRaw("`a1` > 2");
Сравнение столбцов таблиц осуществляется методом whereColumn() и orWhereColumn(), например
// WHERE `a1` = `a2`
$Core_QueryBuilder_Select->whereColumn('a1', '=', 'a2');
ORDER BY
Сортировка задаются методами orderBy(), orderByRaw(). Для удаления предыдущих установок сортировки используется clearOrderBy()
Сортировка по одному полю:
$Core_QueryBuilder_Select
->clearOrderBy()
->orderBy('id', 'ASC');
Сортировка по двум полям, при этом по полю name использовать бинарную сортировку:
$Core_QueryBuilder_Select
->clearOrderBy()
->orderBy('name', 'ASC', TRUE)
->orderBy('sorting', 'DESC');
Случайная сортировка:
$Core_QueryBuilder_Select
->clearOrderBy()
->orderBy('RAND()');
Сырая сортировка для сложных вычислений:
$Core_QueryBuilder_Select
->clearOrderBy()
->orderByRaw('`field1` + `field2` ASC');
HAVING
// HAVING `a1` > '2'
$Core_QueryBuilder_Select->having('a1', '>', '2');
// HAVING `a4` IN (17, 19, NULL, 'NULL')
$Core_QueryBuilder_Select->having('a4', 'IN', array(17,19, NULL, 'NULL'));
// HAVING `a7` BETWEEN 1 AND 10
$Core_QueryBuilder_Select->having('a7', 'BETWEEN', array(1, 10));
Несколько условий, вызванных подряд, будут соединяться с использованием AND, если вы хотите указать OR, то можно между методами вызвать setOr() или использовать метод ->orHaving():
// HAVING `a1` > 2 OR `a2` < 100
$Core_QueryBuilder_Select->having('a1', '>', 2)->setOr()->having('a2', '<', 100);
// второй вариант
$Core_QueryBuilder_Select->having('a1', '>', 2)->orHaving('a2', '<', 100);
Сырое задание условия HAVING осуществляется методом havingRaw(), например
// HAVING `a1` > 2
$Core_QueryBuilder_Select->havingRaw("`a1` > 2");
Сложные условия выборки
$oCore_QueryBuilder_Select = Core_QueryBuilder::select(
array('id', 'newid'), array('name', 'newname'), 'description'
)
->from('structures')
->open()
->where('url', 'LIKE', '%xyz%')
->setOr()
->where('type', '=', 0)
->close()
->where('sorting', '>', 10)
->havingOpen()
->having('newid', '>', 123)
->setOr()
->having('newname', 'LIKE', '%asd%')
->havingClose()
->offset(10)
->limit(5);
Сложные выражения
Указать вычисляемые поля или условия выборки можно с использованием Core_QueryBuilder::raw (Core_QueryBuilder::expression является синонимом), например:
->select(array(Core_QueryBuilder::raw('COUNT(DISTINCT `tablename`.`id`)'), 'count'))
->where('tablename1.field1', '=', Core_QueryBuilder::raw('tablename2.field1'))
->orderBy(Core_QueryBuilder::raw('IF ( `min_weight` > 0 AND `max_weight` > 0 AND `min_price` > 0 AND `max_price` > 0, 1, 0)'), 'DESC')
GROUP BY
// GROUP BY `field`, COUNT(`id`)
$Core_QueryBuilder_Select->groupBy('field1')->groupBy('COUNT(id)');
UNION
Объединение используется для комбинации результатов нескольких запросов на выборку.
// (SELECT `id2`, `name2` FROM `tablename2`)
// UNION
// (SELECT `id`, `name` FROM `tablename` LIMIT 10 OFFSET 0)
$select1 = Core_QueryBuilder::select('id', 'name')->from('tablename')
->limit(0, 10);
$select2 = Core_QueryBuilder::select('id2', 'name2')->from('tablename2')
->union($select1);
WITH
Оператор WITH в MySQL (с версии 8.0) служит для создания временного общего табличного выражения (Common Table Expression, CTE), которое можно затем включить в SQL-запрос, это упрощает построение сложных запросов. * с версии HostCMS 7.1.4
В простейшем виде выражение может быть задано ->with('cte', $cte), либо с указанием столбцов ->with('cte', 'col1', 'col2', $cte), например:
// WITH
// `cte` (`col1`, `col2`) AS (
// (SELECT 1 AS `col1`, 2 AS `col2`)
// UNION
// (SELECT 3, 4)
// )
// SELECT `col1`, `col2`
// FROM `cte`
$cte = Core_QueryBuilder::select(array(1, 'col1'), array(2, 'col2'))
->union(
Core_QueryBuilder::select(3, 4)
);
Core_QueryBuilder::select('col1', 'col2')
//->with('cte', $cte)
->with('cte', 'col1', 'col2', $cte)
->from('cte')
->execute();
Использование партиций
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
С версии MySQL 5.7 при SELECT и JOIN допускается указание названий партиций, из которых делать выборку. Вы можете явно указать имена партиций следующим образом:
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()
->from('structures')
->partition('structures', array('p1', 'p2'));
Указание индексов
https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
В запросах с использованием SELECT и JOIN поддерживается возможность указывать оптимизатору какой индекс использовать. Для этого служат директивы USE {INDEX|KEY}, {IGNORE|FORCE} {INDEX|KEY}.
Указать директивы можно методом indexHint(), который принимает 2 или 3 параметра. Первый - имя таблицы, второй (необязательный) для чего использовать: FOR {JOIN|ORDER BY|GROUP BY}, третий - имя индекса или индексов.
$oCore_QueryBuilder_Select = Core_QueryBuilder::select()
->from('structures')
->indexHint('structures', array('USE INDEX', 'myindex1'))
->indexHint('structures', array('IGNORE INDEX', 'FOR JOIN', 'PRIMARY'));
Получение данных из таблицы частями
Метод chunk() используется при обработке больших объемов данных, он получает заданное количество записей за раз и отправляет массив данных в замыкание для последующей обработки.
Если вы хотите прервать выполнение последующих блоков, то верните FALSE.
Core_QueryBuilder::select()->from('structures')->asAssoc()->chunk(500, function ($aRows) {
foreach ($aRows as $row)
{
var_dump($row);
}
});
Вставка
Первый вариант
$oCore_QueryBuilder_Insert = Core_QueryBuilder::insert('tableName')
->columns('column1', 'column2', 'column3')
->values('value1', 'value2', 11)
->values('value3', 'value4', 17)
->values('value5', 'value6', 19)
->execute();
Второй вариант
$oCore_QueryBuilder_Insert = Core_QueryBuilder::insert('tableName', array('column1' => 'value1', 'column2' => 'value2'))
->execute();
Обновление
// UPDATE `tableName` SET `column1` = 'value', `column2` = 'value2'
// WHERE `column` != '5' AND `a4` IN (17, 19, NULL) ORDER BY `column2` ASC LIMIT 10
$update = Core_QueryBuilder::update('tableName')
->columns(array('column1' => 'value', 'column2' => 'value2'))
->where('column', '!=', '5')
->where('a4', 'IN', array(17,19, NULL))
->orderBy('column2')
->limit(10)
->execute();
// UPDATE `tableName` SET `column1` = 'value', `column2` = 'value2' WHERE `column` = '5'
$oCore_QueryBuilder_Update = Core_QueryBuilder::update('tableName')
->set('column1', 'value')
->set('column2', 'value2')
->where('column', '=', '5')
->execute();
Удаление записи
// DELETE LOW_PRIORITY QUICK IGNORE FROM `tableName`
// WHERE `column1` = '17' AND `column2` != '19'
// ORDER BY `field1` DESC LIMIT 10
$delete = Core_QueryBuilder::delete('tableName')
->lowPriority()
->quick()
->ignore()
->where('column1', '=', '17')
->where('column2', '!=', '19')
->orderBy('field1', 'DESC')
->limit(10)
->execute();
Переименование таблиц
$oCore_QueryBuilder_Rename = Core_QueryBuilder::rename('oldTableName1', 'newTableName1')
->execute();
Переименование нескольких таблиц
// Rename multiple tables
$oCore_QueryBuilder_Rename = Core_QueryBuilder::rename('oldTableName1', 'newTableName1')
->table('oldTableName2', 'newTableName2')
->execute();
Удаление таблицы
// Drop multiple tables
$oCore_QueryBuilder_Drop = Core_QueryBuilder::drop('TableName1')
->table('TableName2')
->execute();
Очистка таблицы
$oCore_QueryBuilder_Truncate = Core_QueryBuilder::truncate('TableName')
->execute();
Комментарии
-
Условия с псевдонимами
Здравствуйте, а как написать следующее выражение?
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name AND n1.property_id=125
-
Условные операторы
Правильно ли я понимаю, что на текущий момент Core_QueryBuilder не поддерживает синтаксис условных операторов CASE WHEN THEN ELSE MySQL ? И что в случаях необходимости нужно использовать метод whereRaw() ?
-
Условия со скобками
А как записать выражение:
WHERE (a IS NULL AND b>0) OR a>0Без темы
->open()
->where('a', 'IS', NULL)
->where('b', '>', 0)
->close()
->setOr()
->where('a', '>', 0)
-
Без темы
Поддерживается выборка:
->where('field', 'IN', array(1,2,3))
а поддерживается ли
->where('field', 'NOT IN', array(1,2,3))
?
-
Без темы
Стоит упомянуть, что перед использованием orderBy() со стандартными модулями системы, часто предварительно нужно очистить сортировку методом ->clearOrderBy()
Например:
$Shop_Controller_Show
->shopItems()
->queryBuilder()
->clearOrderBy()
->orderBy('shop_items.shop_group_id')
