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

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

Несколько условий, вызванных подряд, будут соединяться с использованием 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);

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

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

Комментарии