/**
* Find model instances by ids
* @param int[] $ids
* @return mixed
*/
public function findMany($ids)
{
$query = new Builder(null);
$query->setDI($this->di);
$query->from($this->class);
return $query->inWhere('id', $ids)->getQuery()->execute();
}
public function get(\Phalcon\Mvc\Model\Query\Builder $builder, $page)
{
$config = $this->di->getConfig()->pager;
// TODO: obkumać dlaczego przestało działać, może pojawi się nowa wersja inkubatora.
$adapter = new \Phalcon\Paginator\Adapter\QueryBuilder(array('builder' => $builder, 'limit' => $config->limit, 'page' => $page));
$adapter = new \Phalcon\Paginator\Adapter\Model(array('data' => $builder->getQuery()->execute(), 'limit' => intval($config->limit), 'page' => $page));
return new \Phalcon\Paginator\Pager($adapter, array('layoutClass' => 'Phalcon\\Paginator\\Pager\\Layout\\Bootstrap', 'rangeLength' => intval($config->length), 'urlMask' => '?page={%page_number}'));
}
/**
* @param BaseRepository $repository
* @param Builder $query
* @param string $orderDirection
*/
public function order($repository, $query, $orderDirection)
{
$name = $this->name();
if ($repository->hasColumn($name)) {
$query->orderBy($query->getFrom() . ".{$name} {$orderDirection}");
} else {
$query->orderBy("{$name} {$orderDirection}");
}
}
/**
* @param BaseRepository $repository
* @param Builder $query
* @param string $search
*/
public function search($repository, $query, $search)
{
$name = $this->name();
if ($repository->hasColumn($name)) {
$query->orWhere($query->getFrom() . ".{$name} LIKE '%{$search}%'");
} else {
$query->orWhere("{$name} LIKE '%{$search}%'");
}
}
/**
* @param BaseRepository $repository
* @param mixed $column
* @param Builder $query
* @param string $search
*/
public function apply($repository, $column, $query, $search)
{
$name = $column->name();
if ($repository->hasColumn($name)) {
$query->andWhere($query->getFrom() . ".{$name} LIKE '%{$search}%'");
} else {
$query->andWhere("{$name} LIKE '%{$search}%'");
}
}
/**
* Get builder associated with table of this model.
*
* @param string|null $tableAlias Table alias to use in query.
*
* @return Builder
*/
public static function getBuilder($tableAlias = null)
{
$builder = new Builder();
$table = get_called_class();
if (!$tableAlias) {
$builder->from($table);
} else {
$builder->addFrom($table, $tableAlias);
}
return $builder;
}
/**
* Initialize form.
*
* @return void
*/
public function initialize()
{
$builder = new Builder();
$builder->columns(['scope'])->from('Core\\Model\\LanguageTranslation')->distinct(true);
$result = $builder->getQuery()->execute();
$data = [];
foreach ($result as $row) {
$data[$row->scope] = $row->scope;
}
$this->addMultiSelect('scope', 'Scope', 'Select scopes of translations to export.', $data, $data);
}
/**
* Get main select builder.
*
* @return Builder
*/
public function getSource()
{
$builder = new Builder();
$builder->from('Core\\Model\\LanguageTranslation')->where('language_id = ' . $this->_language->getId());
$showUntranslated = (bool) $this->getDI()->getRequest()->get('untranslated', 'int', 0);
if ($showUntranslated) {
$builder->where("original = translated");
}
if ($search = $this->getDI()->getRequest()->get('search')) {
$builder->where("original LIKE '%{$search}%'")->orWhere("translated LIKE '%{$search}%'");
}
return $builder;
}
public function where($conditions, $bindParams = null, $bindTypes = null)
{
$currentConditions = $this->_conditions;
/**
* Nest the condition to current ones or set as unique
*/
if ($currentConditions) {
$conditions = "(" . $currentConditions . ") AND (" . $conditions . ")";
}
return parent::where($conditions, $bindParams, $bindTypes);
}
/**
* Get main select builder.
*
* @return Builder
*/
public function getSource()
{
$builder = new Builder();
$builder->from(['u' => 'User\\Model\\User'])->columns(['u.*', 'r.name'])->leftJoin('User\\Model\\Role', 'u.role_id = r.id', 'r')->orderBy('u.id DESC');
return $builder;
}
/**
* Generate a PHQL SELECT statement for an aggregate
*
* @param string $function
* @param string $alias
* @param mixed $parameters
* @return \Phalcon\Mvc\Model\ResultsetInterface
* @throws Exception
*/
protected static function _groupResult($function, $alias, $parameters = null)
{
if (is_string($function) === false || is_string($alias) === false) {
throw new Exception('Invalid parameter type.');
}
if (is_array($parameters) === false) {
if (is_null($parameters) === false) {
$params = array($parameters);
} else {
$params = array();
}
} else {
$params = $parameters;
}
if (isset($params['column']) === true) {
$groupColumn = $params['column'];
} else {
$groupColumn = '*';
}
//Builds the column to query according to the received parameters
if (isset($params['distinct']) === true) {
$columns = $function . '(DISTINCT ' . $params['distinct'] . ') AS ' . $alias;
} else {
if (isset($params['group']) === true) {
$columns = $params['group'] . ', ' . $function . '(' . $params['group'] . ') AS ' . $alias;
} else {
$columns = $function . '(' . $groupColumn . ') AS ' . $alias;
}
}
//Builds a query with the passed parameters
$builder = new Builder($params);
$builder->columns($columns);
$builder->from(get_called_class());
$query = $builder->getQuery();
$bindParams = null;
$bindTypes = null;
//Check for bind parameters
if (isset($params['bind']) === true) {
$bindParams = $params['bind'];
if (isset($params['bindTypes']) === true) {
$bindTypes = $params['bindTypes'];
}
}
//Execute the query
$resultset = $query->execute($bindParams, $bindTypes);
//Pass the cache options to the query
if (isset($params['cache']) === true) {
$query->cache($params['cache']);
}
//Return the full resultset if the query is grouped
if (isset($params['group']) === true) {
return $resultset;
}
//Return only the value in the first result
//$number_rows = count($resultset); @note this variable is not necessary
$firstRow = $resultset->getFirst();
return $firstRow->alias;
}
/**
* Get main select builder.
*
* @return Builder
*/
public function getSource()
{
$builder = new Builder();
$builder->from('User\\Model\\Role');
return $builder;
}
private function createSearchBuilder()
{
$builder = new Builder();
$builder->columns(['ping.id as pingId', 'ping.httpCode', 'ping.duration', 'b.id as batchId', 'b.name', 'c.id as countryId', 'c.code as proxyCountryCode', 'u.id as urlId', 'u.address as urlAddress', 'pr.id as proxyId', 'pr.address as proxyAddress']);
$builder->addFrom('ping');
$builder->innerJoin('proxy', 'pr.id = ping.proxyId', 'pr');
$builder->innerJoin('country', 'c.id = pr.countryId', 'c');
$builder->innerJoin('batch', 'b.id = ping.batchId', 'b');
$builder->innerJoin('url', 'u.id = b.urlId', 'u');
return $builder;
}
/**
* @param BaseRepository $repository
* @param Builder $query
* @param string $orderDirection
*/
public function order($repository, $query, $orderDirection)
{
$name = $this->name();
$query->orderBy("count({$name}.id) {$orderDirection}");
}
<?php
namespace Spec\Adapters;
use Phalcon\Mvc\Model\Query\Builder;
use DataTables\Adapters\QueryBuilder;
use DataTables\ParamsParser;
describe("QueryBuilder", function () {
beforeEach(function () {
$builder = new Builder();
$builder->columns('id, name, email, balance')->from("Spec\\Models\\User");
$this->builder = $builder;
});
it("should work withot any filter", function () {
$dataTables = new QueryBuilder(20);
$dataTables->setBuilder($this->builder);
$dataTables->setParser(new ParamsParser(10));
$response = $dataTables->getResponse();
expect($dataTables->getParser())->toBeA('object');
expect(array_keys($response))->toBe(['draw', 'recordsTotal', 'recordsFiltered', 'data']);
expect($response['recordsTotal'])->toBe(100);
expect($response['recordsFiltered'])->toBe(100);
expect(count($response['data']))->toBe(10);
foreach ($response['data'] as $data) {
expect(array_keys($data))->toBe(['id', 'name', 'email', 'balance', 'DT_RowId']);
expect($data['DT_RowId'])->toBe($data['id']);
}
});
describe("Limit&Offset", function () {
beforeEach(function () {
$_GET = ['start' => 2, 'length' => 1];
public function getResponse()
{
$builder = new PQueryBuilder(['builder' => $this->builder, 'limit' => 1, 'page' => 1]);
$total = $builder->getPaginate();
$this->bind('global_search', function ($column, $search) {
$this->builder->orWhere("{$column} LIKE :key_{$column}:", ["key_{$column}" => "%{$search}%"]);
});
$this->bind('column_search', function ($column, $search) {
$this->builder->andWhere("{$column} LIKE :key_{$column}:", ["key_{$column}" => "%{$search}%"]);
});
$this->bind('order', function ($order) {
if (!empty($order)) {
$this->builder->orderBy(implode(', ', $order));
}
});
$builder = new PQueryBuilder(['builder' => $this->builder, 'limit' => $this->parser->getLimit(), 'page' => $this->parser->getPage()]);
/* @var $filtered \Phalcon\Mvc\Model\Resultset */
$filtered = $builder->getPaginate();
/* @var $metadata \Phalcon\Mvc\Model\MetaData */
$metadata = \Phalcon\Di::getDefault()->get('modelsMetadata');
$item = $filtered->items->getFirst();
if ($item instanceof \Phalcon\Mvc\Model) {
$filtered->items->rewind();
$columnMap = $metadata->getAttributes($item);
$columnMap = array_combine($columnMap, $columnMap);
$extractMethods = function ($item) {
$reflection = new \ReflectionClass($item);
$itemMethods = $reflection->getMethods(\ReflectionMethod::IS_PUBLIC);
$itemMethods = array_map(function (\ReflectionMethod $reflectionMethod) {
return $reflectionMethod->getName();
}, $itemMethods);
return array_combine($itemMethods, $itemMethods);
};
// if use array_diff we can catch error, because $this->originalColumns can have array item
$attributes = $methods = [];
foreach ($this->originalColumns as $itemColumn) {
$itemData = [];
if (is_string($itemColumn)) {
// check that it is item attribute
if (isset($columnMap[$itemColumn])) {
$attributes[] = $itemColumn;
}
} elseif (is_array($itemColumn)) {
/**
* Possible variants
* itemColumn => [methodName => [param1, param2]] - method with parameters
* itemColumn => methodName] - method without parameters
*
*/
$columnName = array_keys($itemColumn)[0];
$methodData = $itemColumn[$columnName];
if (!isset($columnMap[$columnName])) {
// undefined columnName
//continue;
}
$parameters = null;
if (is_array($methodData)) {
$methodName = array_keys($methodData)[0];
$parameters = $methodData[$methodName];
} else {
$methodName = $methodData;
}
// check that it is existed method
if (empty($itemMethods)) {
$itemMethods = $extractMethods($item);
}
if (isset($itemMethods[$methodName])) {
$methods[$columnName] = compact('methodName', 'parameters');
}
}
}
$data = [];
foreach ($filtered->items as $item) {
$itemData = [];
foreach ($attributes as $attr) {
$itemData[$attr] = $item->readAttribute($attr);
}
foreach ($methods as $columnName => $method) {
$parameters = !empty($method['parameters']) ? $method['parameters'] : [];
$itemData[$columnName] = call_user_func_array([$item, $method['methodName']], $parameters);
}
$data[] = $itemData;
}
} else {
$data = $filtered->items->toArray();
}
return $this->formResponse(['total' => $total->total_items, 'filtered' => $filtered->total_items, 'data' => $data]);
}
/**
* @param \stdClass $pager
* @param Query\Builder $builder
* @param integer $limit
* @return \stdClass
*
* 因为Phalcon的分页，在连表查询时候，会产生bug，导致分页出错，所以用此方法来对其重新赋值
*/
public function correctPaginator(\stdClass $pager, \Phalcon\Mvc\Model\Query\Builder $builder, $limit)
{
$builderArray = $builder->getQuery()->execute()->toArray();
$newPaginator = new PurePaginator($limit, count($builderArray), $builderArray);
$pager->before = $newPaginator->before;
$pager->first = $newPaginator->first;
$pager->next = $newPaginator->next;
$pager->last = $newPaginator->last;
$pager->current = $newPaginator->current;
$pager->total_items = $newPaginator->total_items;
$pager->total_pages = $newPaginator->total_pages;
$pager->page_range = $newPaginator->page_range;
$pager->next_range = $newPaginator->next_range;
$pager->prev_range = $newPaginator->prev_range;
return $pager;
}
public function testAction()
{
$di = $this->_getDI();
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots]');
$builder = new Builder();
$phql = $builder->setDi($di)->from(array('Robots', 'RobotsParts'))->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].*, [RobotsParts].* FROM [Robots], [RobotsParts]');
$builder = new Builder();
$phql = $builder->setDi($di)->columns('*')->from('Robots')->getPhql();
$this->assertEquals($phql, 'SELECT * FROM [Robots]');
$builder = new Builder();
$phql = $builder->setDi($di)->columns(array('id', 'name'))->from('Robots')->getPhql();
$this->assertEquals($phql, 'SELECT id, name FROM [Robots]');
$builder = new Builder();
$phql = $builder->setDi($di)->columns('id')->from('Robots')->getPhql();
$this->assertEquals($phql, 'SELECT id FROM [Robots]');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->where('Robots.name = "Voltron"')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE Robots.name = "Voltron"');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->where('Robots.name = "Voltron"')->andWhere('Robots.id > 100')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE (Robots.name = "Voltron") AND (Robots.id > 100)');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->where('Robots.name = "Voltron"')->orWhere('Robots.id > 100')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE (Robots.name = "Voltron") OR (Robots.id > 100)');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->where(100)->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE [Robots].[id] = 100');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->groupBy('Robots.name')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] GROUP BY Robots.name');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->groupBy(array('Robots.name', 'Robots.id'))->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] GROUP BY Robots.name, Robots.id');
$builder = new Builder();
$phql = $builder->setDi($di)->columns(array('Robots.name', 'SUM(Robots.price)'))->from('Robots')->groupBy('Robots.name')->getPhql();
$this->assertEquals($phql, 'SELECT Robots.name, SUM(Robots.price) FROM [Robots] GROUP BY Robots.name');
$builder = new Builder();
$phql = $builder->setDi($di)->columns(array('Robots.name', 'SUM(Robots.price)'))->from('Robots')->groupBy('Robots.name')->having('SUM(Robots.price) > 1000')->getPhql();
$this->assertEquals($phql, 'SELECT Robots.name, SUM(Robots.price) FROM [Robots] GROUP BY Robots.name HAVING SUM(Robots.price) > 1000');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->join('RobotsParts')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts]');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->join('RobotsParts', null, 'p')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts] AS [p]');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts] AS [p] ON Robots.id = RobotsParts.robots_id');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p')->join('Parts', 'Parts.id = RobotsParts.parts_id', 't')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts] AS [p] ON Robots.id = RobotsParts.robots_id JOIN [Parts] AS [t] ON Parts.id = RobotsParts.parts_id');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->leftJoin('RobotsParts', 'Robots.id = RobotsParts.robots_id')->leftJoin('Parts', 'Parts.id = RobotsParts.parts_id')->where('Robots.id > 0')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] LEFT JOIN [RobotsParts] ON Robots.id = RobotsParts.robots_id LEFT JOIN [Parts] ON Parts.id = RobotsParts.parts_id WHERE Robots.id > 0');
$builder = new Builder();
$phql = $builder->setDi($di)->addFrom('Robots', 'r')->getPhql();
$this->assertEquals($phql, 'SELECT [r].* FROM [Robots] AS [r]');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->addFrom('Parts', 'p')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].*, [p].* FROM [Robots], [Parts] AS [p]');
$builder = new Builder();
$phql = $builder->setDi($di)->from(array('r' => 'Robots'))->addFrom('Parts', 'p')->getPhql();
$this->assertEquals($phql, 'SELECT [r].*, [p].* FROM [Robots] AS [r], [Parts] AS [p]');
$builder = new Builder();
$phql = $builder->setDi($di)->from(array('r' => 'Robots', 'p' => 'Parts'))->getPhql();
$this->assertEquals($phql, 'SELECT [r].*, [p].* FROM [Robots] AS [r], [Parts] AS [p]');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->orderBy('Robots.name')->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] ORDER BY Robots.name');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->orderBy(array(1, 'Robots.name'))->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] ORDER BY 1, Robots.name');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->limit(10)->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] LIMIT 10');
$builder = new Builder();
$phql = $builder->setDi($di)->from('Robots')->limit(10, 5)->getPhql();
$this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] LIMIT 10 OFFSET 5');
}
public function applyQuery(\Phalcon\Mvc\Model\Query\Builder $builder, Query $query, ApiResource $resource)
{
$from = $builder->getFrom();
$fromString = is_array($from) ? array_keys($from)[0] : $from;
if ($query->hasFields()) {
$builder->columns($query->getFields());
}
if ($query->hasOffset()) {
$builder->offset($query->getOffset());
}
if ($query->hasLimit()) {
$builder->limit($query->getLimit());
}
if ($query->hasConditions()) {
$conditions = $query->getConditions();
$andConditions = [];
$orConditions = [];
/** @var Condition $condition */
foreach ($conditions as $conditionIndex => $condition) {
if ($condition->getType() == Condition::TYPE_AND) {
$andConditions[] = $condition;
} else {
if ($condition->getType() == Condition::TYPE_OR) {
$orConditions[] = $condition;
}
}
}
$allConditions = $orConditions + $andConditions;
/** @var Condition $condition */
foreach ($allConditions as $conditionIndex => $condition) {
$operator = $this->getOperator($condition->getOperator());
if (!$operator) {
continue;
}
$parsedValues = $this->parseValues($operator, $condition->getValue());
$format = $this->getConditionFormat($operator);
$valuesReplacementString = $this->getValuesReplacementString($parsedValues, $conditionIndex);
$fieldString = sprintf('[%s].[%s]', $fromString, $condition->getField());
$conditionString = sprintf($format, $fieldString, $operator, $valuesReplacementString);
$bindValues = $this->getBindValues($parsedValues, $conditionIndex);
switch ($condition->getType()) {
case Condition::TYPE_OR:
$builder->orWhere($conditionString, $bindValues);
break;
case Condition::TYPE_AND:
default:
$builder->andWhere($conditionString, $bindValues);
break;
}
}
}
if ($query->hasExcludes()) {
$builder->notInWhere($fromString . '.' . $resource->getModelPrimaryKey(), $query->getExcludes());
}
if ($query->hasSorters()) {
$sorters = $query->getSorters();
/** @var Sorter $sorter */
foreach ($sorters as $sorter) {
switch ($sorter->getDirection()) {
case Sorter::DESCENDING:
$direction = 'DESC';
break;
case Sorter::ASCENDING:
default:
$direction = 'ASC';
break;
}
$fieldString = sprintf('[%s].[%s]', $fromString, $sorter->getField());
$builder->orderBy($fieldString . ' ' . $direction);
}
}
}
/**
* Get main select builder.
*
* @return Builder
*/
public function getSource()
{
$builder = new Builder();
$builder->from('Core\\Model\\Page');
return $builder;
}
public function testGroup()
{
$this->specify("Query Builders don't work with a GROUP BY statement", function () {
$di = $this->di;
$builder = new Builder();
$phql = $builder->setDi($di)->columns(["name", "SUM(price)"])->from(Robots::class)->groupBy("id, name")->getPhql();
expect($phql)->equals("SELECT name, SUM(price) FROM [" . Robots::class . "] GROUP BY [id], [name]");
});
}
/**
* Apply offset and limit to the query
* @param Builder $query
*/
protected function applyOffset($query)
{
$offset = $this->di->get('request')->getQuery('start', 'int', 0);
$limit = $this->di->get('request')->getQuery('length', 'int', 10);
if ($limit == -1) {
return;
}
$query->limit($limit, $offset);
}
/**
* Apply sorting data on array.
*
* @param Builder $source Data.
*
* @return array|void
*/
protected function _applySorting(Builder $source)
{
$sort = $this->_getParam('sort');
$direction = $this->_getParam('direction', 'DESC');
// Additional checks.
if (!$sort || $direction != 'DESC' && $direction != 'ASC') {
return;
}
$source->orderBy(sprintf('%s %s', $sort, $direction));
}
/**
* Test checks passing 'condition' query param into constructor.
* Conditions can now be passed as an string(as before) and
* as an array of 3 elements:
* - condition string for example "age > :age: AND created > :created:"
* - bind params for example array('age' => 18, 'created' => '2013-09-01')
* - bind types for example array('age' => PDO::PARAM_INT, 'created' => PDO::PARAM_STR)
*
* First two params are REQUIRED, bind types are optional.
*/
public function testConstructorConditions()
{
require PATH_CONFIG . 'config.db.php';
if (empty($configMysql)) {
$this->markTestSkipped("Test skipped");
return;
}
$di = $this->_getDI();
// ------------- test for setters(classic) way ----------------
$standardBuilder = new Builder();
$standardBuilder->from('Robots')->where("year > :min: AND year < :max:", array("min" => '2013-01-01', 'max' => '2100-01-01'), array("min" => \PDO::PARAM_STR, 'max' => \PDO::PARAM_STR));
$standardResult = $standardBuilder->getQuery()->execute();
// --------------- test for single condition ------------------
$params = array('models' => 'Robots', 'conditions' => array(array("year > :min: AND year < :max:", array("min" => '2013-01-01', 'max' => '2100-01-01'), array("min" => \PDO::PARAM_STR, 'max' => \PDO::PARAM_STR))));
$builderWithSingleCondition = new Builder($params);
$singleConditionResult = $builderWithSingleCondition->getQuery()->execute();
// ------------- test for multiple conditions ----------------
$params = array('models' => 'Robots', 'conditions' => array(array("year > :min:", array("min" => '2000-01-01'), array("min" => \PDO::PARAM_STR)), array("year < :max:", array('max' => '2100-01-01'), array("max" => \PDO::PARAM_STR))));
// conditions are merged!
$builderMultipleConditions = new Builder($params);
$multipleConditionResult = $builderMultipleConditions->getQuery()->execute();
$expectedPhql = "SELECT [Robots].* FROM [Robots] " . "WHERE year > :min: AND year < :max:";
/* ------------ ASSERTING --------- */
$this->assertEquals($expectedPhql, $standardBuilder->getPhql());
$this->assertInstanceOf("Phalcon\\Mvc\\Model\\Resultset\\Simple", $standardResult);
$this->assertEquals($expectedPhql, $builderWithSingleCondition->getPhql());
$this->assertInstanceOf("Phalcon\\Mvc\\Model\\Resultset\\Simple", $singleConditionResult);
$this->assertEquals($expectedPhql, $builderMultipleConditions->getPhql());
$this->assertInstanceOf("Phalcon\\Mvc\\Model\\Resultset\\Simple", $multipleConditionResult);
}
/**
* Get main select builder.
*
* @return Builder
*/
public function getSource()
{
$builder = new Builder();
$builder->from('Core\\Model\\Menu')->columns(['id', 'name']);
return $builder;
}
public function testGroup()
{
require 'unit-tests/config.db.php';
if (empty($configMysql)) {
$this->markTestSkipped("Test skipped");
return;
}
$di = $this->_getDI();
$builder = new Builder();
$phql = $builder->setDi($di)->columns(array('name', 'SUM(price)'))->from('Robots')->groupBy('id, name')->getPhql();
$this->assertEquals($phql, 'SELECT name, SUM(price) FROM [Robots] GROUP BY [id], [name]');
}
private function createSearchBuilder()
{
$builder = new Builder();
$builder->columns(['ping.id', 'ping.batchId', 'ping.proxyId', 'ping.httpCode', 'ping.duration', 'ping.createdAt', 'ping.updatedAt', 'ping.error', 'b.urlId', 'b.name', 'u.address as urlAddress', 'pr.address as proxyAddress']);
$builder->addFrom('ping');
$builder->innerJoin('proxy', 'pr.id = ping.proxyId', 'pr');
$builder->innerJoin('batch', 'b.id = ping.batchId', 'b');
$builder->innerJoin('url', 'u.id = b.urlId', 'u');
return $builder;
}
/**
* Добавляет ->limit в $builder.
* @param \Phalcon\Mvc\Model\Query\Builder $builder
* @param array $params
* @return \Phalcon\Mvc\Model\Query\Builder
*/
private static function limit(Builder $builder, array $params)
{
if (empty($params['limit'])) {
return $builder;
}
$limit = (int) $params['limit'];
$builder->limit($limit);
if (empty($params['offset'])) {
return $builder;
}
$offset = (int) $params['offset'];
return $builder->offset($offset);
}
/**
* @param Builder $query
*/
public function apply($query)
{
$name = $this->name();
$value = $this->value();
$query->andWhere("{$name} = {$value}");
}
/**
* Sets the columns to be queried
*
* @param string|array $columns
* @return \Engine\Mvc\Model\Query\Builder
*/
public function columns($columns)
{
if (!$columns) {
return $this;
}
if (is_string($columns)) {
if (strpos(strtolower($columns), "rowcount") !== false) {
parent::columns($columns);
return $this;
}
$columns = [$columns];
}
$this->_columns = [];
foreach ($columns as $alias => $column) {
if (is_array($column)) {
$useTableAlias = isset($column['useTableAlias']) ? $column['useTableAlias'] : true;
$useCorrelationName = isset($column['useCorrelationName']) ? $column['useCorrelationName'] : false;
$this->setColumn($column[0], $alias, $useTableAlias, $useCorrelationName);
} else {
$this->setColumn($column, $alias);
}
}
return $this;
}