/**
* 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);
}
/**
* @param \Phalcon\Mvc\Model\Query\Builder $builder
* @param array $excludeColumns
*/
private static function setExcludeColumns($builder, $excludeColumns)
{
$modelName = $builder->getFrom();
/**
* @var \Phalcon\Mvc\Model $model
*/
$model = new $modelName();
$columns = $model->getModelsMetaData()->getAttributes($model);
$columns = array_diff($columns, $excludeColumns);
foreach ($columns as $key => $column) {
foreach ($excludeColumns as $excludeColumn) {
$excludeColumn = Text::lower($excludeColumn);
if (!Text::endsWith($excludeColumn, '*')) {
break;
}
$excludeColumn = str_replace('*', '', $excludeColumn);
$column = Text::lower($column);
$isStartWith = Text::startsWith($column, $excludeColumn);
if ($isStartWith) {
unset($columns[$key]);
break;
}
}
}
$builder->columns($columns);
}
<?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];
/**
* Get main select builder.
*
* @return Builder
*/
public function getSource()
{
$builder = new Builder();
$builder->columns(['u.*', 'r.name'])->addFrom('User\\Model\\User', 'u')->leftJoin('User\\Model\\Role', 'u.role_id = r.id', 'r')->orderBy('u.id DESC');
return $builder;
}
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);
}
}
}
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;
}
/**
* 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;
}
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;
}
/**
* 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;
}
