$qb = $conn->createQueryBuilder()
->select('u')
->from('users', 'u')
->where('u.id = :user_id')
->setParameter(':user_id', 1);
/**
* @return array
*/
public function fetch()
{
$data = $this->query->execute()->fetchAll(\PDO::FETCH_KEY_PAIR);
$keys = array_keys($data);
$this->query->setParameter(':lastId', array_pop($keys));
return array_values($data);
}
/**
* {@inheritdoc}
*/
public function apply($fromAlias, $fromIdentifier, $resourcePrefix, array $requesterIdentifiers, $mask, array $orX = [])
{
$this->queryBuilder->leftJoin($fromAlias, $this->getAclSchema()->getPermissionsTableName(), 'acl_p', 'acl_p.resource = ' . $this->connection->getDatabasePlatform()->getConcatExpression(':acl_prefix', $fromAlias . '.' . $fromIdentifier));
$orX[] = 'acl_p.requester IN (:acl_identifiers) AND :acl_mask = (acl_p.mask & :acl_mask)';
$this->queryBuilder->andWhere(implode(' OR ', $orX));
$this->queryBuilder->setParameter('acl_prefix', $resourcePrefix, \PDO::PARAM_STR)->setParameter('acl_identifiers', $requesterIdentifiers, Connection::PARAM_STR_ARRAY)->setParameter('acl_mask', $mask, \PDO::PARAM_INT);
return $this->queryBuilder;
}
/**
* paramTypes is private, we need to redefine setParameter
* to access it
*
* @param string $key
* @param string $value
* @param string $type
*
* @return $this
*/
public function setParameter($key, $value, $type = null)
{
if ($type !== null) {
$this->paramTypes[$key] = $type;
}
return parent::setParameter($key, $value, $type);
}
public function match(QueryBuilder $qb)
{
if ($this->has('username')) {
$qb->andWhere('u.username = :username');
$qb->setParameter('username', $this->get('username'));
}
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
if (!$this->includeAllGroups) {
$query->andWhere('g.gID > :minGroupID');
$query->setParameter('minGroupID', REGISTERED_GROUP_ID);
}
return $query;
}
/**
* Applies operation to data source and returns modified data source.
*
* @param QueryBuilder $src
* @param OperationInterface|FilterOperation $operation
* @return QueryBuilder
*/
public function process($src, OperationInterface $operation)
{
$value = $operation->getValue();
$operator = $operation->getOperator();
$fieldName = $operation->getField();
$parameterName = 'p' . md5($fieldName . $operator);
$src->andWhere("{$fieldName} {$operator} :{$parameterName}");
$src->setParameter($parameterName, $value);
return $src;
}
/**
* @see \ComPHPPuebla\Doctrine\TableGateway\Specification\QueryBuilderSpecification::match()
*/
public function match(QueryBuilder $qb)
{
if ($this->has('latitude') && $this->has('longitude')) {
$qb->addSelect(<<<SELECT
(6371
* (2 * ATAN(SQRT(SIN(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
+ COS(:latitude * (PI()/180)) * COS(s.latitude * (PI()/180))
* SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2)),
SQRT(1-(sin(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
+ COS(:latitude * (PI()/180) * COS(s.latitude * (PI()/180)
* SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2))))))))
AS distance
SELECT
);
$qb->orderBy('distance');
$qb->setParameter('latitude', $this->get('latitude'));
$qb->setParameter('longitude', $this->get('longitude'));
}
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
if (!$this->includeInactiveUsers) {
$query->andWhere('u.uIsActive = :uIsActive');
$query->setParameter('uIsActive', true);
}
if (!$this->includeUnvalidatedUsers) {
$query->andWhere('u.uIsValidated != 0');
}
return $query;
}
/**
* Sets the values and parameters of the upcoming given query according
* to the entity.
*
* @param Entity $entity
* the entity with its fields and values
* @param QueryBuilder $queryBuilder
* the upcoming query
* @param string $setMethod
* what method to use on the QueryBuilder: 'setValue' or 'set'
*/
protected function setValuesAndParameters(Entity $entity, QueryBuilder $queryBuilder, $setMethod)
{
$formFields = $this->getFormFields();
$count = count($formFields);
for ($i = 0; $i < $count; ++$i) {
$type = $this->definition->getType($formFields[$i]);
$value = $entity->get($formFields[$i]);
if ($type == 'boolean') {
$value = $value ? 1 : 0;
}
$queryBuilder->{$setMethod}('`' . $formFields[$i] . '`', '?');
$queryBuilder->setParameter($i, $value);
}
}
/**
* {@inheritdoc}
*/
public function buildQuery(QueryBuilder $queryBuilder, array $values = array())
{
if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
return;
}
$codes = $values[$this->getName()];
if (in_array('all_but_paris', $codes)) {
$departements = new Departments();
$regions = new Regions();
unset($codes[array_search('all_but_paris', $codes)]);
$codes = array_merge($codes, array_diff(array_keys($departements->getArrayCopy()), $regions->get(self::ALL_BUT_PARIS_REGION_CODE)->getCodesDepartements()));
}
if (count($codes)) {
$queryBuilder->setParameter('department', $codes, Connection::PARAM_STR_ARRAY)->andWhere('response.companyDepartment IN(:department)');
}
}
/**
* Sets the values and parameters of the upcoming given query according
* to the entity.
*
* @param CRUDEntity $entity
* the entity with its fields and values
* @param QueryBuilder $queryBuilder
* the upcoming query
* @param boolean $setValue
* whether to use QueryBuilder::setValue (true) or QueryBuilder::set (false)
*/
protected function setValuesAndParameters(CRUDEntity $entity, QueryBuilder $queryBuilder, $setValue)
{
$formFields = $this->definition->getEditableFieldNames();
$count = count($formFields);
for ($i = 0; $i < $count; ++$i) {
$value = $entity->get($formFields[$i]);
$type = $this->definition->getType($formFields[$i]);
if ($type == 'bool') {
$value = $value ? 1 : 0;
}
if ($type == 'date' || $type == 'datetime' || $type == 'reference') {
$value = $value == '' ? null : $value;
}
if ($setValue) {
$queryBuilder->setValue('`' . $formFields[$i] . '`', '?');
} else {
$queryBuilder->set('`' . $formFields[$i] . '`', '?');
}
$queryBuilder->setParameter($i, $value);
}
}
/**
* @param array $filters
* @param QueryBuilder $queryBuilder
* @param array $filterDefinitions
*
* @return bool
*/
private function applyFilters(array $filters, QueryBuilder $queryBuilder, array $filterDefinitions)
{
$expr = $queryBuilder->expr();
$filterExpr = $expr->andX();
if (count($filters)) {
foreach ($filters as $filter) {
$exprFunction = isset($filter['expr']) ? $filter['expr'] : $filter['condition'];
$paramName = InputHelper::alphanum($filter['column']);
switch ($exprFunction) {
case 'notEmpty':
$filterExpr->add($expr->isNotNull($filter['column']));
$filterExpr->add($expr->neq($filter['column'], $expr->literal('')));
break;
case 'empty':
$filterExpr->add($expr->isNull($filter['column']));
$filterExpr->add($expr->eq($filter['column'], $expr->literal('')));
break;
default:
if (trim($filter['value']) == '') {
// Ignore empty
break;
}
$columnValue = ":{$paramName}";
switch ($filterDefinitions[$filter['column']]['type']) {
case 'bool':
case 'boolean':
if ((int) $filter['value'] > 1) {
// Ignore the "reset" value of "2"
break;
}
$queryBuilder->setParameter($paramName, $filter['value'], 'boolean');
break;
case 'float':
$columnValue = (double) $filter['value'];
break;
case 'int':
case 'integer':
$columnValue = (int) $filter['value'];
break;
default:
$queryBuilder->setParameter($paramName, $filter['value']);
}
$filterExpr->add($expr->{$exprFunction}($filter['column'], $columnValue));
}
}
}
if ($filterExpr->count()) {
$queryBuilder->andWhere($filterExpr);
return true;
}
return false;
}
/**
* @param QueryBuilder $query
* @return QueryBuilder
*/
protected function prepareConditions($query)
{
$i = 0;
if (count($this->getConditions()) !== 0) {
foreach ($this->getConditions() as $condition) {
$conditionKey = 'NNXGridCondition_' . $i;
$query->andWhere($condition->getKey() . ' ' . $condition->getCriteria() . ' :' . $conditionKey);
$query->setParameter($conditionKey, $condition->getValue());
$i++;
}
}
return $query;
}
/**
* {@inheritdoc}
*/
public function notEquals($field, $value)
{
$this->queryBuilder->setParameter($field, $value);
return $this->queryBuilder->expr()->neq($field, ':' . $field);
}
/**
* Modify the query to count how many rows is between a range of date diff in seconds
*
* @param QueryBuilder $query
* @param string $dateColumn1
* @param string $dateColumn2
* @param integer $startSecond
* @param integer $endSecond
* @param array $filters will be added to where claues
* @param string $tablePrefix
*/
public function modifyCountDateDiffQuery(QueryBuilder &$query, $dateColumn1, $dateColumn2, $startSecond = 0, $endSecond = 60, $tablePrefix = 't')
{
$query->select('COUNT(' . $tablePrefix . '.' . $dateColumn1 . ') AS count');
$query->where('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') >= :startSecond');
$query->andWhere('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') < :endSecond');
$query->setParameter('startSecond', $startSecond);
$query->setParameter('endSecond', $endSecond);
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
if ($this->includeAliases) {
$query->from('Pages', 'p')->leftJoin('p', 'Pages', 'pa', 'p.cPointerID = pa.cID')->leftJoin('p', 'PagePaths', 'pp', 'p.cID = pp.cID and pp.ppIsCanonical = true')->leftJoin('pa', 'PageSearchIndex', 'psi', 'psi.cID = if(pa.cID is null, p.cID, pa.cID)')->leftJoin('p', 'PageTypes', 'pt', 'pt.ptID = if(pa.cID is null, p.ptID, pa.ptID)')->leftJoin('p', 'CollectionSearchIndexAttributes', 'csi', 'csi.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'CollectionVersions', 'cv', 'cv.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->andWhere('p.cIsTemplate = 0 or pa.cIsTemplate = 0');
} else {
$query->from('Pages', 'p')->leftJoin('p', 'PagePaths', 'pp', '(p.cID = pp.cID and pp.ppIsCanonical = true)')->leftJoin('p', 'PageSearchIndex', 'psi', 'p.cID = psi.cID')->leftJoin('p', 'PageTypes', 'pt', 'p.ptID = pt.ptID')->leftJoin('c', 'CollectionSearchIndexAttributes', 'csi', 'c.cID = csi.cID')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->innerJoin('p', 'CollectionVersions', 'cv', 'p.cID = cv.cID')->andWhere('p.cPointerID < 1')->andWhere('p.cIsTemplate = 0');
}
if ($this->pageVersionToRetrieve == self::PAGE_VERSION_RECENT) {
$query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)');
} else {
$query->andWhere('cvIsApproved = 1');
}
if ($this->isFulltextSearch) {
$query->addSelect('match(psi.cName, psi.cDescription, psi.content) against (:fulltext) as cIndexScore');
}
if (!$this->includeInactivePages) {
$query->andWhere('p.cIsActive = :cIsActive');
$query->setParameter('cIsActive', true);
}
if (!$this->includeSystemPages) {
$query->andWhere('p.cIsSystemPage = :cIsSystemPage');
$query->setParameter('cIsSystemPage', false);
}
return $query;
}
/**
* @param QueryBuilder $query
* @param string $column
* @param string $operand
* @param mixed $value
* @param int $filterCount
*/
private function addFilter(QueryBuilder $query, $column, $operand, $value, $filterCount)
{
switch ($operand) {
case self::OPERAND_EQ:
$query->andWhere($query->expr()->eq($column, ':__filter' . $filterCount));
break;
case self::OPERAND_LT:
$query->andWhere($query->expr()->lt($column, ':__filter' . $filterCount));
break;
case self::OPERAND_LTE:
$query->andWhere($query->expr()->lte($column, ':__filter' . $filterCount));
break;
case self::OPERAND_GT:
$query->andWhere($query->expr()->gt($column, ':__filter' . $filterCount));
break;
case self::OPERAND_GTE:
$query->andWhere($query->expr()->gte($column, ':__filter' . $filterCount));
break;
case self::OPERAND_LIKE:
$query->andWhere($query->expr()->like($column, ':__filter' . $filterCount));
break;
case self::OPERAND_LIKE_CI:
$query->andWhere($query->expr()->like('LOWER(' . $column . ')', ':__filter' . $filterCount));
$value = strtolower($value);
break;
}
$query->setParameter('__filter' . $filterCount, $value);
}
/**
* @param QueryBuilder $query
* @param string $suffix
*/
private function addConfiguratorTranslationWithSuffix(QueryBuilder $query, $suffix = '')
{
$selectSuffix = !empty($suffix) ? '_' . strtolower($suffix) : '';
$query->leftJoin('configuratorGroup', 's_core_translations', 'configuratorGroupTranslation' . $suffix, 'configuratorGroupTranslation' . $suffix . '.objecttype = :configuratorGroupType AND
configuratorGroupTranslation' . $suffix . '.objectkey = configuratorGroup.id AND
configuratorGroupTranslation' . $suffix . '.objectlanguage = :language' . $suffix);
$query->leftJoin('configuratorOption', 's_core_translations', 'configuratorOptionTranslation' . $suffix, 'configuratorOptionTranslation' . $suffix . '.objecttype = :configuratorOptionType AND
configuratorOptionTranslation' . $suffix . '.objectkey = configuratorOption .id AND
configuratorOptionTranslation' . $suffix . '.objectlanguage = :language' . $suffix);
$query->setParameter(':configuratorGroupType', 'configuratorgroup')->setParameter(':configuratorOptionType', 'configuratoroption');
$query->addSelect(['configuratorGroupTranslation' . $suffix . '.objectdata as __configuratorGroup_translation' . $selectSuffix, 'configuratorOptionTranslation' . $suffix . '.objectdata as __configuratorOption_translation' . $selectSuffix]);
}
/**
* Load the requested resources into RBAC.
*
* @param Rbac $rbac
* @param string $role
* @param string|null $permission
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
protected function load($rbac, $role, $permission = null)
{
$options = $this->options;
$builder = new QueryBuilder($this->connection);
// Role always present
$builder->select('node.name')->from($options->getRoleTable(), 'node')->from($options->getRoleTable(), 'parent')->where('node.lft BETWEEN parent.lft AND parent.rgt')->andWhere('parent.name = :role')->orderBy('node.lft');
$builder->setParameter('role', $role);
// Permission optional
if ($permission) {
$builder->addSelect('permission.name AS permission')->leftJoin('node', 'role_permission', 'rp', 'node.id = rp.role_id')->leftJoin('node', 'permission', 'permission', 'rp.permission_id = permission.id')->andWhere('(permission.name = :permission OR permission.name IS NULL)');
$builder->setParameter('permission', $permission);
}
$parent = null;
foreach ($builder->execute() as $row) {
if ($parent) {
if (!$rbac->hasRole($row['name'])) {
$rbac->getRole($parent)->addChild($row['name']);
}
} elseif (!$rbac->hasRole($row['name'])) {
$rbac->addRole($row['name']);
}
if ($permission) {
if ($row['permission']) {
$rbac->getRole($row['name'])->addPermission($row['permission']);
}
}
$parent = $row['name'];
}
return $builder;
}
protected function prepareQuery(QueryBuilder $queryBuilder)
{
$expr = $queryBuilder->expr();
if (count($this->filteredGroups)) {
foreach ($this->filteredGroups as $index => $filteredGroup) {
$condition = $filteredGroup['membersGroupFilter_condition'];
$group = $filteredGroup['membersGroupFilter_group'];
switch ($condition) {
case 'in':
$where = $expr->orX();
// HHFIX
// $where->add($expr->like('m.groups', ':groupPattern1_' . $index));
// HHENDFIX
$where->add($expr->like('m.groups', ':groupPattern2_' . $index));
break;
case 'not in':
$where = $expr->andX();
// HHFIX
// $where->add($expr->notLike('m.groups', ':groupPattern1_' . $index));
// ENDFIX
$where->add($expr->notLike('m.groups', ':groupPattern2_' . $index));
break;
default:
continue 2;
}
$queryBuilder->andWhere($where)->setParameter('groupPattern2_' . $index, sprintf('%%s:%d:"%d";%%', strlen($group), $group));
}
}
if (count($this->filteredMailingLists)) {
$queryBuilder->innerJoin('m', 'orm_avisota_subscription', 's', 's.recipientType = :recipientType AND s.recipientId = m.id')->setParameter('recipientType', 'member');
$or = $expr->orX();
foreach ($this->filteredMailingLists as $index => $mailingList) {
$or->add($expr->eq('s.mailingList', ':mailingList' . $index));
$queryBuilder->setParameter('mailingList' . $index, $mailingList->getId());
}
$queryBuilder->andWhere($or);
}
if (count($this->filteredProperties)) {
foreach ($this->filteredProperties as $index => $filteredProperty) {
$property = 'm.' . $filteredProperty['membersPropertyFilter_property'];
$comparator = $filteredProperty['membersPropertyFilter_comparator'];
$value = $filteredProperty['membersPropertyFilter_value'];
switch ($comparator) {
case 'empty':
$queryBuilder->andWhere($expr->orX($expr->eq($property, ':property' . $index), $expr->isNull($property)));
$value = '';
break;
case 'not empty':
$queryBuilder->andWhere($expr->gt($property, ':property' . $index));
$value = '';
break;
case 'eq':
$queryBuilder->andWhere($expr->eq($property, ':property' . $index));
break;
case 'neq':
$queryBuilder->andWhere($expr->neq($property, ':property' . $index));
break;
case 'gt':
$queryBuilder->andWhere($expr->gt($property, ':property' . $index));
break;
case 'gte':
$queryBuilder->andWhere($expr->gte($property, ':property' . $index));
break;
case 'lt':
$queryBuilder->andWhere($expr->lt($property, ':property' . $index));
break;
case 'lte':
$queryBuilder->andWhere($expr->lte($property, ':property' . $index));
break;
}
$queryBuilder->setParameter(':property' . $index, $value);
}
}
}
/**
* Apply date filters to the query.
*
* @param QueryBuilder $query
* @param string $dateColumn
* @param string $tablePrefix
*/
public function applyDateFilters(QueryBuilder $queryBuilder, $dateColumn, $tablePrefix = 't', $dateOnly = false)
{
if ($tablePrefix) {
$tablePrefix .= '.';
}
if (empty($this->options['dateFrom'])) {
$this->options['dateFrom'] = new \DateTime();
$this->options['dateFrom']->modify('-30 days');
}
if (empty($this->options['dateTo'])) {
$this->options['dateTo'] = new \DateTime();
}
if ($dateOnly) {
$queryBuilder->andWhere('DATE(' . $tablePrefix . $dateColumn . ') BETWEEN :dateFrom AND :dateTo');
$queryBuilder->setParameter('dateFrom', $this->options['dateFrom']->format('Y-m-d'));
$queryBuilder->setParameter('dateTo', $this->options['dateTo']->format('Y-m-d'));
} else {
$queryBuilder->andWhere($tablePrefix . $dateColumn . ' BETWEEN :dateFrom AND :dateTo');
$queryBuilder->setParameter('dateFrom', $this->options['dateFrom']->format('Y-m-d H:i:s'));
$queryBuilder->setParameter('dateTo', $this->options['dateTo']->format('Y-m-d H:i:s'));
}
}
/**
* @test
*/
public function setParameterDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->setParameter(Argument::exact('aField'), Argument::exact(5), Argument::cetera())->shouldBeCalled()->willReturn($this->subject);
$this->subject->setParameter('aField', 5);
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
if ($this->includeAliases) {
$query->from('Pages', 'p')->leftJoin('p', 'Pages', 'pa', 'p.cPointerID = pa.cID')->leftJoin('p', 'PagePaths', 'pp', 'p.cID = pp.cID and pp.ppIsCanonical = true')->leftJoin('pa', 'PageSearchIndex', 'psi', 'psi.cID = if(pa.cID is null, p.cID, pa.cID)')->leftJoin('p', 'PageTypes', 'pt', 'pt.ptID = if(pa.cID is null, p.ptID, pa.ptID)')->leftJoin('p', 'CollectionSearchIndexAttributes', 'csi', 'csi.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'CollectionVersions', 'cv', 'cv.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->andWhere('p.cIsTemplate = 0 or pa.cIsTemplate = 0');
} else {
$query->from('Pages', 'p')->leftJoin('p', 'PagePaths', 'pp', '(p.cID = pp.cID and pp.ppIsCanonical = true)')->leftJoin('p', 'PageSearchIndex', 'psi', 'p.cID = psi.cID')->leftJoin('p', 'PageTypes', 'pt', 'p.ptID = pt.ptID')->leftJoin('c', 'CollectionSearchIndexAttributes', 'csi', 'c.cID = csi.cID')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->innerJoin('p', 'CollectionVersions', 'cv', 'p.cID = cv.cID')->andWhere('p.cPointerID < 1')->andWhere('p.cIsTemplate = 0');
}
switch ($this->pageVersionToRetrieve) {
case self::PAGE_VERSION_RECENT:
$query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)');
break;
case self::PAGE_VERSION_RECENT_UNAPPROVED:
$query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)')->andWhere('cvIsApproved = 0');
break;
case self::PAGE_VERSION_ACTIVE:
default:
$query->andWhere('cvIsApproved = 1');
break;
}
if ($this->isFulltextSearch) {
$query->addSelect('match(psi.cName, psi.cDescription, psi.content) against (:fulltext) as cIndexScore');
}
if (!$this->includeInactivePages) {
$query->andWhere('p.cIsActive = :cIsActive');
$query->setParameter('cIsActive', true);
}
if (is_object($this->siteTree)) {
$tree = $this->siteTree;
} else {
$site = \Core::make("site")->getSite();
$tree = $site->getSiteTreeObject();
}
// Note, we might not use this. We have to set the parameter even if we don't use it because
// StackList (which extends PageList) needs to have it available.
$query->setParameter('siteTreeID', $tree->getSiteTreeID());
if ($this->query->getParameter('cParentID') < 1) {
if (!$this->includeSystemPages) {
$query->andWhere('p.siteTreeID = :siteTreeID');
$query->andWhere('p.cIsSystemPage = :cIsSystemPage');
$query->setParameter('cIsSystemPage', false);
} else {
$query->andWhere('(p.siteTreeID = :siteTreeID or p.siteTreeID = 0)');
}
}
return $query;
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$query->andWhere('e.exEntryEntityID = :entityID');
$query->setParameter('entityID', $this->entity->getID());
return $query;
}
/**
* @param array $request
* @return array
*/
public function make(array $request)
{
$output = ['data' => [], 'draw' => $request['draw'], 'recordsFiltered' => 0, 'recordsTotal' => 0];
/**
* Order By
*/
if (isset($request['order'])) {
for ($i = 0; $i < count($request['order']); ++$i) {
$j = intval($request['order'][$i]['column']);
if ($request['columns'][$j]['orderable'] != 'true') {
continue;
}
$column = $request['columns'][$j]['data'];
$sort = $request['order'][$i]['dir'];
$this->query->addOrderBy($column, $sort);
}
}
/**
* Count All
*/
$temp = clone $this->query;
$temp->resetQueryPart('select');
$temp->resetQueryPart('orderBy');
$temp->select("COUNT(*)");
$output['recordsTotal'] = $temp->execute()->fetchColumn(0);
/**
* Filter
*/
for ($i = 0; $i < count($request['columns']); ++$i) {
if ($request['columns'][$i]['searchable'] != 'true') {
continue;
}
$value = $request['columns'][$i]['search']['value'];
if (strlen($value) > 0) {
$column = $request['columns'][$i]['data'];
$value = $this->query->getConnection()->quote("{$value}%");
$this->query->andHaving($this->query->expr()->like($column, $value));
}
}
/**
* Search
*/
if (isset($request['search'])) {
$value = $request['search']['value'];
if (strlen($value) > 0) {
for ($i = 0; $i < count($request['columns']); ++$i) {
if ($request['columns'][$i]['searchable'] != 'true') {
continue;
}
$column = $request['columns'][$i]['data'];
$this->query->orHaving($this->query->expr()->like($column, ':search'));
}
$this->query->setParameter('search', "%{$value}%");
}
}
/**
* Count Filtered
*/
$temp = clone $this->query;
$temp->resetQueryPart('orderBy');
$output['recordsFiltered'] = $temp->execute()->rowCount();
/**
* Limit
*/
if (isset($request['start'])) {
$this->query->setFirstResult($request['start']);
}
if (isset($request['length'])) {
$this->query->setMaxResults($request['length']);
}
/**
* Fetch Results
*/
$output['data'] = $this->query->execute()->fetchAll(\PDO::FETCH_ASSOC);
/**
* Add Filter
*/
return $output;
}
/**
* @group DBAL-959
*/
public function testGetParameterTypes()
{
$qb = new QueryBuilder($this->conn);
$qb->select('*')->from('users');
$this->assertSame(array(), $qb->getParameterTypes());
$qb->where('name = :name');
$qb->setParameter('name', 'foo');
$this->assertSame(array(), $qb->getParameterTypes());
$qb->setParameter('name', 'foo', \PDO::PARAM_STR);
$qb->where('is_active = :isActive');
$qb->setParameter('isActive', true, \PDO::PARAM_BOOL);
$this->assertSame(array('name' => \PDO::PARAM_STR, 'isActive' => \PDO::PARAM_BOOL), $qb->getParameterTypes());
}
/**
* Sets a query parameter for the query being constructed.
*
* @param string|int $key The parameter position or name.
* @param mixed $value The parameter value.
* @param string|null $type One of the Connection::PARAM_* constants.
*
* @return QueryBuilder This QueryBuilder instance.
*/
public function setParameter($key, $value, string $type = null) : QueryBuilder
{
$this->concreteQueryBuilder->setParameter($key, $value, $type);
return $this;
}
/**
* Creates an expression for the given operator and sets appropriate parameter on the given query builder.
*
* @param QueryBuilder $queryBuilder Query builder to be configured.
* @param string $operator Expression operator.
* @param string $property Property name.
* @param mixed $value Value for the right side of the operator.
*
* @return string
*/
private function expr(QueryBuilder $queryBuilder, $operator, $property, $value)
{
$parameter = $this->createParameter($property);
$expression = $queryBuilder->expr()->{$operator}($property, ':' . $parameter);
$queryBuilder->setParameter($parameter, $value);
return $expression;
}
/**
* Build an OR group that is added to the AND.
*
* @param QueryBuilder $qb
* @param string $parentColumnName
* @param array $options
*
* @return CompositeExpression
*/
protected function buildWhereOr(QueryBuilder $qb, $parentColumnName, array $options)
{
$orX = $qb->expr()->orX();
foreach ($options as $columnName => $option) {
if (empty($options[$columnName])) {
continue;
} elseif (Arr::isIndexedArray($options)) {
$key = $parentColumnName . '_' . $columnName;
$orX->add("{$parentColumnName} = :{$key}");
$qb->setParameter($key, $option);
} else {
$orX->add("{$columnName} = :{$columnName}");
$qb->setParameter($columnName, $option);
}
}
return $orX;
}
/**
* Sets a query parameter for the query being constructed.
*
* <code>
* $qb = $conn->getQueryBuilder()
* ->select('u')
* ->from('users', 'u')
* ->where('u.id = :user_id')
* ->setParameter(':user_id', 1);
* </code>
*
* @param string|integer $key The parameter position or name.
* @param mixed $value The parameter value.
* @param string|null $type One of the IQueryBuilder::PARAM_* constants.
*
* @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
*/
public function setParameter($key, $value, $type = null)
{
$this->queryBuilder->setParameter($key, $value, $type);
return $this;
}
