Построитель запросов 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();

Примеры работы

При использовании "сырых" методов (в названии методов использовано raw или expression) необходимо самостоятельно выполнять экранирование всех внешних данных. Если вы не уверены, не используйте такие методы.

Получение данных в виде массива или объектов

Для получения данных в виде массива с массивами, укажите 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'))
	));

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()

// 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));

// 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);

Использование партиций

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();

Не нашли ответ на свой вопрос в документации? Направьте обращение в службу поддержки или онлайн чат.

Условные операторы

Правильно ли я понимаю, что на текущий момент Core_QueryBuilder не поддерживает синтаксис условных операторов CASE WHEN THEN ELSE MySQL ? И что в случаях необходимости нужно использовать метод whereRaw() ?

10.11.2020 11:59:21

lezhenkin

Без темы

Да, верно.

10.11.2020 12:07:01

hostcms

Без темы

Подскажите, Core_QueryBuilder::insert имеет защиту от иньекции? Наподобие statement в PDO?

23.02.2019 22:30:25

volgastar

Без темы

Дополнительное экранирование значений при использовании Core_QueryBuilder::insert / update не требуется.

25.02.2019 13:10:13

hostcms

Условия со скобками

А как записать выражение:
WHERE (a IS NULL AND b>0) OR a>0

27.09.2018 22:48:38

yus

Без темы

->open()
->where('a', 'IS', NULL)
->where('b', '>', 0)
->close()
->setOr()
->where('a', '>', 0)

28.09.2018 10:17:38

alexander.egorov

Без темы

Поддерживается выборка:
->where('field', 'IN', array(1,2,3))

а поддерживается ли
->where('field', 'NOT IN', array(1,2,3))

?

23.07.2014 19:38:32

web-alt

Без темы

Стоит упомянуть, что перед использованием orderBy() со стандартными модулями системы, часто предварительно нужно очистить сортировку методом ->clearOrderBy()

Например:
$Shop_Controller_Show
->shopItems()
->queryBuilder()
->clearOrderBy()
->orderBy('shop_items.shop_group_id')

18.06.2014 10:01:27

Levsha

Построитель запросов QueryBuilder

Совместная работа ORM и QueryBuilder

Примеры работы

Получение данных в виде массива или объектов

Получение массива всех найденных элементов

Получение одного найденного элемента

Указание выбираемых столбцов

Использование псевдонимов

Скалярный подзапрос

Сравнение с использованием подзапроса

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

WHERE

ORDER BY

HAVING

Сложные условия выборки

Сложные выражения

GROUP BY

UNION

Использование партиций

Указание индексов

Получение данных из таблицы частями

Вставка

Обновление

Удаление записи

Переименование таблиц

Удаление таблицы

Очистка таблицы

Комментарии

Условия с псевдонимами

Условные операторы

Без темы

Без темы

Без темы

Условия со скобками

Без темы

Без темы

Без темы