/** * Taken from https://gist.github.com/jgornick/8671644 * * Recursively takes the specified criteria and adds too the expression. * * The criteria is defined in an array notation where each item in the list * represents a comparison <fieldName, operator, value>. The operator maps to * comparison methods located in ExpressionBuilder. The key in the array can * be used to identify grouping of comparisons. * * @example * $criteria = array( * 'or' => array( * array('field1', 'like', '%field1Value%'), * array('field2', 'like', '%field2Value%') * ), * 'and' => array( * array('field3', 'eq', 3), * array('field4', 'eq', 'four') * ), * array('field5', 'neq', 5) * ); * * $qb = new QueryBuilder(); * addCriteria($qb, $qb->expr()->andX(), $criteria); * echo $qb->getSQL(); * * // Result: * // SELECT * * // FROM tableName * // WHERE ((field1 LIKE '%field1Value%') OR (field2 LIKE '%field2Value%')) * // AND ((field3 = '3') AND (field4 = 'four')) * // AND (field5 <> '5') * * @param QueryBuilder $qb * @param CompositeExpression $expr * @param array $criteria */ static function addCriteria(QueryBuilder $qb, $expr, array $criteria) { if (count($criteria)) { // var_dump($criteria); foreach ($criteria as $element) { foreach ($element as $expression => $comparison) { if (!is_array($comparison)) { $comparison = $element; } if ($expression > 0) { continue; } // echo // "Elem:".((is_array($element))?print_r($element,true):$element).'--'. // "Expr:".((is_array($expression))?print_r($expression,true):$expression).'--'. // "Comp:".((is_array($comparison))?print_r($comparison,true):$comparison).'--'. // "<br>"; //break; // echo "<br><br>"; // echo($expression); // echo(print_r($comparison,true)); // if ($comparison) if ($expression === 'or') { $expr->add(Doctrine::addCriteria($qb, $qb->expr()->orX(), $comparison)); } else { if ($expression === 'and') { $expr->add(Doctrine::addCriteria($qb, $qb->expr()->andX(), $comparison)); } else { // print_r($comparison); if (count($comparison) == 3) { list($field, $operator, $value) = $comparison; } elseif (count($comparison) == 2) { list($field, $operator) = $comparison; } if ($operator == 'in') { $expr->add($qb->expr()->{$operator}($field, $value)); } elseif (in_array($operator, ['isNull', 'isNotNull'])) { $expr->add($qb->expr()->{$operator}($field)); } else { $expr->add($qb->expr()->{$operator}($field, $qb->expr()->literal($value))); } } } } // echo $qb->getDql()."<br><br>"; } } return $expr; }
/** * @param QueryBuilder|\Doctrine\ORM\QueryBuilder $q * @param $column * @param $operator * @param $parameter * @param $includeIsNull true/false or null to auto determine based on operator * * @return mixed */ protected function generateFilterExpression($q, $column, $operator, $parameter, $includeIsNull, CompositeExpression $appendTo = null) { // in/notIn for dbal will use a raw array if (!is_array($parameter) && strpos($parameter, ':') !== 0) { $parameter = ":{$parameter}"; } if (null === $includeIsNull) { // Auto determine based on negate operators $includeIsNull = in_array($operator, ['neq', 'notLike', 'notIn']); } if ($includeIsNull) { $expr = $q->expr()->orX($q->expr()->{$operator}($column, $parameter), $q->expr()->isNull($column)); } else { $expr = $q->expr()->{$operator}($column, $parameter); } if ($appendTo) { $appendTo->add($expr); return $appendTo; } return $expr; }
/** * Adds an expression to composite expression. * * @param mixed $part * * @return \OCP\DB\QueryBuilder\ICompositeExpression */ public function add($part) { $this->compositeExpression->add($part); return $this; }
/** * @param QueryBuilder $builder * @param CompositeExpression $link * @param ErrorCollection $errors * @param string $table * @param string $column * @param string $method * @param string|array $params * * @return void * * @SuppressWarnings(PHPMD.ElseExpression) */ protected function applyComparisonMethod(QueryBuilder $builder, CompositeExpression $link, ErrorCollection $errors, $table, $column, $method, $params) { // params could be in form of 1 value or array of values if (is_array($params) === true) { foreach ($params as $param) { if (is_scalar($param) === true) { $param = (string) $builder->createNamedParameter($param); $link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param)); } else { $this->addInvalidQueryParameterError($errors, $column); } } } elseif (is_scalar($params) === true) { $param = $builder->createNamedParameter((string) $params); $link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param)); } else { // parameter is neither array nor string/scalar $this->addInvalidQueryParameterError($errors, $column); } }