public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (isset($this->gID) && $this->gID > 0) {
$query->where('gID = ?')->setParameter($paramcount++, $this->gID);
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('pName', 'ASC');
break;
case "date":
$query->orderBy('pDateAdded', 'DESC');
break;
}
switch ($this->featured) {
case "featured":
$query->andWhere("pFeatured = 1");
break;
case "nonfeatured":
$query->andWhere("pFeatured = 0");
break;
}
if ($this->activeOnly) {
$query->andWhere("pActive = 1");
}
if ($this->search) {
$query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
return $query;
}
/**
* {@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;
}
/**
* @deprecated
*/
public function filter($field, $value, $comparison = '=')
{
if ($field == false) {
$this->query->andWhere($value);
// ugh
} else {
$this->query->andWhere(implode(' ', array($field, $comparison, $this->query->createNamedParameter($value))));
}
}
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;
}
/**
* {@inheritdoc}
*/
public function restrict($expression, $condition = DataSourceInterface::CONDITION_AND)
{
switch ($condition) {
case DataSourceInterface::CONDITION_AND:
$this->queryBuilder->andWhere($expression);
break;
case DataSourceInterface::CONDITION_OR:
$this->queryBuilder->orWhere($expression);
break;
}
}
/**
* {@inheritdoc}
*/
public function buildQuery(QueryBuilder $queryBuilder, array $values = array())
{
if (!array_key_exists('salary', $values)) {
return;
}
//Switch min and max salary if user has inverted the fields
if (isset($values['salary']['min']) && isset($values['salary']['max']) && $values['salary']['max'] < $values['salary']['min']) {
list($values['salary']['min'], $values['salary']['max']) = [$values['salary']['max'], $values['salary']['min']];
}
if (isset($values['salary']['min'])) {
$queryBuilder->andWhere('response.grossAnnualSalary >= :minSalary')->setParameter('minSalary', $values['salary']['min']);
}
if (isset($values['salary']['max'])) {
$queryBuilder->andWhere('response.grossAnnualSalary <= :maxSalary')->setParameter('maxSalary', $values['salary']['max']);
}
}
/**
* @internal
*/
private function incorporateDbalQueryBuilder(QueryBuilder $qb, FilterInterface $filter)
{
$criteria = $this->getFilteringCriteria($filter);
// extraindo os rootAliases, pois o DBAL\QueryBuilder não tem
$fromPart = $qb->getQueryPart('from');
$rootAliases = array();
foreach ($fromPart as $part) {
$rootAliases[] = $part['alias'];
}
$visitor = new DbalQueryExpressionVisitor($qb->getConnection(), $rootAliases, $this->fieldMap);
if ($whereExpression = $criteria->getWhereExpression()) {
$qb->andWhere($visitor->dispatch($whereExpression));
$qb->setParameters($visitor->getParameters());
}
if ($criteria->getOrderings()) {
foreach ($criteria->getOrderings() as $sort => $order) {
$qb->addOrderBy($visitor->getFieldName($sort), $order);
}
}
if (($firstResult = $criteria->getFirstResult()) !== null) {
$qb->setFirstResult($firstResult);
}
if (($maxResults = $criteria->getMaxResults()) !== null) {
$qb->setMaxResults($maxResults);
}
}
public function match(QueryBuilder $qb)
{
if ($this->has('username')) {
$qb->andWhere('u.username = :username');
$qb->setParameter('username', $this->get('username'));
}
}
public function where($column, $operator = null, $value = null, $boolean = 'and')
{
$args = func_get_args();
if (count($args) == 1) {
parent::where($args[0]);
return $this;
}
$column = $args[0];
$boolean = 'and';
$operator = '=';
if (count($args) == 2) {
$value = $args[1];
}
if (count($args) == 3) {
$operator = $args[1];
$value = $args[2];
}
if (count($args) == 4) {
$operator = $args[1];
$value = $args[2];
$boolean = $args[3];
}
if (is_array($value)) {
$operator = $operator == '=' ? 'in' : 'notIn';
$where_clause = $this->expr()->{$operator}($column, parent::createNamedParameter($value, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
} else {
$where_clause = $column . $operator . parent::createNamedParameter($value);
}
if ($boolean == 'and') {
parent::andWhere($where_clause);
} elseif ($boolean == 'or') {
parent::orWhere($where_clause);
}
return $this;
}
/**
* @param $tableName
* @param $conditions
*
* @return array
*
* @throws LookupError
*
* @since 1.1.0
*
* @author Eddilbert Macharia (http://eddmash.com) <*****@*****.**>
*/
public static function filters(QueryBuilder $queryBuilder, $conditions)
{
// default lookup is equal
$lookup = 'eq';
// we add the or conditions afterwards to avoid them being mistaken for "and" conditions when they come first
$or_combine = [];
$and_combine = [];
// create where clause from the conditions given
foreach ($conditions as $condition) {
foreach ($condition as $key => $value) {
$column = self::getLookupColumn($key);
$lookup = self::getLookUP($key);
$value = self::prepareValue($value, $lookup);
echo self::$lookuOptions[$lookup] . '<br>';
echo $queryBuilder->createNamedParameter($value) . '<br>';
echo $value . '<br>';
$lookupCondition = sprintf(self::$lookuOptions[$lookup], $queryBuilder->createNamedParameter($value));
$queryString = sprintf('%s %s', $column, $lookupCondition);
if (self::combine($key) === self::$or) {
$queryBuilder->orWhere($queryString);
} else {
$queryBuilder->andWhere($queryString);
}
}
}
}
/**
* {@inheritdoc}
*/
public function buildQuery(QueryBuilder $queryBuilder, array $values = array())
{
if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
return;
}
$queryBuilder->andWhere($queryBuilder->expr()->in('response.companyType', $values[$this->getName()]));
}
/**
* Builds the raw query
*
* @return void
*/
protected function buildQuery()
{
$this->queryBuilder = $this->connection->createQueryBuilder();
$this->queryBuilder->select((array) $this->conf->select);
/*
* Main table
*/
$this->queryBuilder->from($this->conf->from->name, $this->conf->from->alias);
/*
* Inner join, right join, left join
*/
$joinTypes = ['innerJoin', 'leftJoin', 'rightJoin'];
foreach ($joinTypes as $joinType) {
if (isset($this->conf->{$joinType})) {
$joins = $this->conf->{$joinType};
$this->buildJoins($joinType, $joins);
}
}
/*
* Condition
*/
if (isset($this->conf->where)) {
foreach ($this->conf->where as $where) {
$this->queryBuilder->andWhere($where);
}
}
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (isset($this->gID) && $this->gID > 0) {
$query->where('gID = ?')->setParameter($paramcount++, $this->gID);
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('drName', 'ASC');
break;
}
if ($this->search) {
$query->andWhere('drName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
$query->andWhere('drDeleted is NULL');
return $query;
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
if (!$this->includeAllGroups) {
$query->andWhere('g.gID > :minGroupID');
$query->setParameter('minGroupID', REGISTERED_GROUP_ID);
}
return $query;
}
/**
* Parses the `CriteriaExpression` and appropriately configures the passed query builder.
*
* @param QueryBuilder $queryBuilder Query builder to be configured.
* @param CriteriaExpression|null $criteria Criteria to be parsed.
*
* @return QueryBuilder
*/
public function parse(QueryBuilder $queryBuilder, CriteriaExpression $criteria = null)
{
if (!$criteria || count($criteria->getCriteria()) === 0) {
return $queryBuilder;
}
$queryBuilder->andWhere($this->parseCriteria($queryBuilder, $criteria));
return $queryBuilder;
}
/**
* @inheritdoc
*/
public function __call($name, $arguments)
{
$operator = $this->operator($name);
if (!$operator) {
throw new \BadMethodCallException('Call to undefined method ' . get_class($this) . '::' . $name . '()');
}
$condition = $operator($this, ...$arguments);
$this->queryBuilder->andWhere($condition);
return $this;
}
/**
* 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;
}
/**
* {@inheritdoc}
*/
public function buildQuery(QueryBuilder $queryBuilder, array $values = [])
{
if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
return;
}
$campaigns = array_map(function (Campaign $item) {
return $item->getId();
}, $values[$this->getName()]->toArray());
$queryBuilder->andWhere($queryBuilder->expr()->in('response.campaign_id', $campaigns));
}
/**
* Build the query with active filters
*
* @param QueryBuilder $queryBuilder
* @param array $values
*/
public function buildQuery(QueryBuilder $queryBuilder, array $values = [])
{
if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
return;
}
$condition = array_map(function ($value) use($queryBuilder) {
if ($value === '') {
return $queryBuilder->expr()->isNull('response.gender');
}
return $queryBuilder->expr()->eq('response.gender', $value);
}, $values[$this->getName()]);
$queryBuilder->andWhere(new CompositeExpression(CompositeExpression::TYPE_OR, $condition));
}
private function addWhereIn($where)
{
$conditionName = $this->getConditionName($where);
if (empty($this->conditions[$conditionName]) or !is_array($this->conditions[$conditionName])) {
return $this;
}
$marks = array();
foreach (array_values($this->conditions[$conditionName]) as $index => $value) {
$marks[] = ":{$conditionName}_{$index}";
$this->conditions["{$conditionName}_{$index}"] = $value;
}
$where = str_replace(":{$conditionName}", implode(',', $marks), $where);
return parent::andWhere($where);
}
/**
* @param QueryBuilder $qb
* @param string $field
* @param ArrayValuesContains $value
*
* @return array
*/
public function applyWhereCriteria(QueryBuilder $qb, $field, $value)
{
$parametersToSet = [];
// hack to reduce count of fetched records
// TODO - hack for NotEmpty
foreach ($value->getValues() as $criterionValue) {
if (!is_string($criterionValue)) {
continue;
}
$qb->andWhere($qb->expr()->like($field, '?'));
$parametersToSet[] = '%' . $criterionValue . '%';
}
return $parametersToSet;
}
/**
* @param QueryBuilder $qb
* @param $fields
* @param Like $value
*
* @return null|array parameters to set to DBAL
*/
public function applyWhereCriteria(QueryBuilder $qb, $fields, $value)
{
$likeExpressions = [];
$parameters = [];
$searchValue = $value->getValue();
// for this criterion we handle multiple fields
foreach (explode('|', $fields) as $field) {
$likeExpressions[] = $qb->expr()->like($field, '?');
$parameters[] = "%{$searchValue}%";
}
$whereExpr = call_user_func_array([$qb->expr(), 'orX'], $likeExpressions);
$qb->andWhere($whereExpr);
return $parameters;
}
/**
* Filter field with `LIKE`.
*
* @param string $filterName
* @param string $field
*/
protected function likeFilter($filterName, $field)
{
$info = $this->extract($filterName);
$info['values'] = explode(',', str_replace('*', '%', $info['values']));
foreach ($info['values'] as $value) {
if ($info['cond']) {
$expr = $this->expr->like($field, "'%{$value}%'");
} else {
$expr = $this->expr->notLike($field, "'%{$value}%'");
}
}
$this->builder->andWhere($expr);
$this->filters[$filterName] = $info;
}
/**
* Add the additional query conditions returned by the QueryRestrictionBuilder
* to the current query and return the original set of conditions so that they
* can be restored after the query has been built/executed.
*
* @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed
*/
protected function addAdditonalWhereConditions()
{
$queryRestrictionBuilder = GeneralUtility::makeInstance(QueryRestrictionBuilder::class, $this->getQueriedTables(), $this->expr(), $this->getQueryContext());
$originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
if ($originalWhereConditions instanceof CompositeExpression) {
$originalWhereConditions = clone $originalWhereConditions;
}
$additionalQueryRestrictions = $queryRestrictionBuilder->getVisibilityConstraints();
if ($additionalQueryRestrictions->count() !== 0) {
// save the original query conditions so we can restore
// them after the query has been built.
$this->concreteQueryBuilder->andWhere($additionalQueryRestrictions);
}
return $originalWhereConditions;
}
protected function addWhereIn($where)
{
$conditionName = $this->getConditionName($where);
if (empty($this->conditions[$conditionName]) || !is_array($this->conditions[$conditionName])) {
return $this;
}
$this->conditions[$conditionName] = array_unique($this->conditions[$conditionName]);
$marks = array();
foreach (array_values($this->conditions[$conditionName]) as $index => $value) {
$marks[] = ":{$conditionName}_{$index}";
$this->conditions["{$conditionName}_{$index}"] = $value;
}
$where = str_replace(":{$conditionName}", join(',', $marks), $where);
return parent::andWhere($where);
}
protected function processWhereCondition(array $condition, QBuilder $qbuilder)
{
$query = $condition['condition'];
if (stripos($query, '?') === false) {
$query .= " = ?";
}
$type = isset($condition['type']) ? strtoupper($condition['type']) : 'AND';
if ($type == 'AND') {
$qbuilder->andWhere($query);
} else {
$qbuilder->orWhere($query);
}
if (isset($condition['value'])) {
$this->params[] = $condition['value'];
}
}
/**
* Add the additional query conditions returned by the QueryRestrictionBuilder
* to the current query and return the original set of conditions so that they
* can be restored after the query has been built/executed.
*
* @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed
*/
protected function addAdditionalWhereConditions()
{
$originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
$expression = $this->restrictionContainer->buildExpression($this->getQueriedTables(), $this->expr());
// This check would be obsolete, as the composite expression would not add empty expressions anyway
// But we keep it here to only clone the previous state, in case we really will change it.
// Once we remove this state preserving functionality, we can remove the count check here
// and just add the expression to the query builder.
if ($expression->count() > 0) {
if ($originalWhereConditions instanceof CompositeExpression) {
// Save the original query conditions so we can restore
// them after the query has been built.
$originalWhereConditions = clone $originalWhereConditions;
}
$this->concreteQueryBuilder->andWhere($expression);
}
// @todo add hook to be able to add additional restrictions
return $originalWhereConditions;
}
/**
* Add required WHERE parameters.
*
* @param QueryBuilder $qb
*/
protected function addWhereActivity(QueryBuilder $qb, $options)
{
if (empty($options)) {
return;
}
foreach ($options as $columnName => $option) {
if (is_array($options[$columnName])) {
$qb->andWhere($this->buildWhereOr($qb, $columnName, $option));
} elseif (!empty($options[$columnName])) {
$qb->andWhere("{$columnName} = :{$columnName}")->setParameter($columnName, $option);
}
}
}
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;
}
private function addBetweenFilter(QueryBuilder $query)
{
$query->andWhere($query->expr()->gte('main.age', ':age_min'))->andWhere($query->expr()->lt('main.age', ':age_max'))->setParameter('age_min', 30)->setParameter('age_max', 50);
}