QueryBuilder реализует Database Abstraction Layer (DBAL) для следующий операторов: Select, Insert, Update, Delete, Rename, Drop, Truncate, Lock.
Использование QueryBuilder возможно как отдельно для выполнения соответствующих запросов, так и совместно с ORM.
Объект 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 `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 `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 `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(), 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');
Сортировка задаются методами 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 `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 `field`, COUNT(`id`) $Core_QueryBuilder_Select->groupBy('field1')->groupBy('COUNT(id)');
Объединение используется для комбинации результатов нескольких запросов на выборку.
// (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();
Здравствуйте, а как написать следующее выражение?
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')