$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; }