/** * 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; }