/**
* {@inheritdoc}
*/
public function addRestriction($restriction, $condition, $isComputed = false)
{
if ($isComputed) {
throw new \LogicException('The HAVING restrictions is not supported yet.');
}
if ($this->currentExpr === null) {
// this is the first item in a group
$this->currentExpr = $restriction;
} else {
// other items
if ($condition === FilterUtility::CONDITION_OR) {
if ($this->currentExpr instanceof Expr\Orx) {
$this->currentExpr->add($restriction);
} else {
$this->currentExpr = $this->qb->expr()->orX($this->currentExpr, $restriction);
}
} else {
if ($this->currentExpr instanceof Expr\Andx) {
$this->currentExpr->add($restriction);
} else {
$this->currentExpr = $this->qb->expr()->andX($this->currentExpr, $restriction);
}
}
}
}
public function addFilterCondition(Composite $sub, QueryBuilder $query)
{
$unique = time();
$q = new Comparison('a.name', Comparison::EQ, '?' . $unique);
$query->setParameter($unique, $this->attribute);
$sub->add($q);
}
/**
* Add new condition to remove ace by share scope
*
* @param QueryBuilder $qb
* @param Composite $expr
* @param string $scope
*
* @throws UnknownShareScopeException
*/
protected function addExprByShareScope(QueryBuilder $qb, Composite $expr, $scope)
{
if ($scope === Share::SHARE_SCOPE_USER) {
$expr->add($qb->expr()->eq('asid.username', 'true'));
} elseif ($scope === Share::SHARE_SCOPE_BUSINESS_UNIT) {
$expr->add($qb->expr()->like('asid.identifier', $qb->expr()->literal('Oro\\\\Bundle\\\\OrganizationBundle\\\\Entity\\\\BusinessUnit%')));
} else {
throw new UnknownShareScopeException($scope);
}
}
/**
* 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 Composite $expr
* @param array $criteria
*/
private static function _addCriteria($qb, Composite $expr, array $criteria)
{
if (count($criteria)) {
foreach ($criteria as $expression => $comparison) {
list($field, $operator, $value) = $comparison;
// MINIMUM VALIDATION (to prevent silly access to database) - BEGIN
if (is_string($operator)) {
if (!in_array($operator, ['and', 'or', 'like', 'eq', 'ne', 'lt', 'le', 'gt', 'ge', 'in', 'out'])) {
throw new \Exception('RQL: Operator not supported!');
}
if (!in_array($operator, ['and', 'or']) && is_null($value)) {
throw new \Exception('RQL: Value cannot be empty!');
}
if (in_array($operator, ['like', 'eq', 'ne', 'lt', 'le', 'gt', 'ge']) && is_array($value)) {
throw new \Exception('RQL: Value type cannot be Array!');
}
if ($operator == 'like' && !is_string($value)) {
throw new \Exception('RQL: Value type must be String!');
}
if ($operator == 'like' && ($value == 'true()' || $value == 'false()')) {
throw new \Exception('RQL: Value type cannot be boolean!');
}
if (in_array($operator, ['lt', 'le', 'gt', 'ge']) && !is_numeric($value)) {
throw new \Exception('RQL: Value type must be Numeric!');
}
if (in_array($operator, ['like', 'eq', 'ne', 'lt', 'le', 'gt', 'ge']) && trim($value) == '') {
throw new \Exception('RQL: Value cannot be an empty String/Number!');
}
if (in_array($operator, ['in', 'out']) && !is_array($value)) {
throw new \Exception('RQL: Value type must be Array!');
}
if (in_array($operator, ['in', 'out']) && count($value) == 0) {
throw new \Exception('RQL: Value type cannot be an empty Array!');
}
if (in_array($operator, ['in', 'out'])) {
foreach ($value as $v) {
if (is_array($v)) {
throw new \Exception('RQL: Value cannot be a multidimensional Array!');
}
if (is_null($v)) {
throw new \Exception('RQL: Value cannot have null Array elements!');
}
if (trim($v) == '') {
throw new \Exception('RQL: Value cannot have empty Array elements!');
}
if ($v == 'null()') {
throw new \Exception('RQL: Value cannot have null Array elements!');
}
}
}
}
// MINIMUM VALIDATION - END
if ($expression === 'and') {
$expr->add(self::_addCriteria($qb, $qb->expr()->andX(), $comparison));
} else {
if ($expression === 'or') {
$expr->add(self::_addCriteria($qb, $qb->expr()->orX(), $comparison));
} else {
switch ($operator) {
case 'like':
$field = 'LOWER(' . $field . ')';
if ($value == 'null()') {
$expr->add($qb->expr()->isNull($field));
} elseif ($value == 'empty()') {
$value = $qb->expr()->literal('');
$expr->add($qb->expr()->{$operator}($field, $value));
} else {
$value = $qb->expr()->literal(strtolower($value));
$expr->add($qb->expr()->{$operator}($field, $value));
}
break;
case 'eq':
if ($value == 'null()') {
$expr->add($qb->expr()->isNull($field));
} elseif ($value == 'true()') {
$value = $qb->expr()->literal(TRUE);
$expr->add($qb->expr()->{$operator}($field, $value));
} elseif ($value == 'false()') {
$value = $qb->expr()->literal(FALSE);
$expr->add($qb->expr()->{$operator}($field, $value));
} elseif ($value == 'empty()') {
$value = $qb->expr()->literal('');
$expr->add($qb->expr()->{$operator}($field, $value));
} else {
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
}
break;
case 'ne':
$operator = 'neq';
if ($value == 'null()') {
$expr->add($qb->expr()->isNotNull($field));
} elseif ($value == 'true()') {
$value = $qb->expr()->literal(TRUE);
$expr->add($qb->expr()->{$operator}($field, $value));
} elseif ($value == 'false()') {
$value = $qb->expr()->literal(FALSE);
$expr->add($qb->expr()->{$operator}($field, $value));
} elseif ($value == 'empty()') {
$value = $qb->expr()->literal('');
$expr->add($qb->expr()->{$operator}($field, $value));
} else {
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
}
break;
case 'lt':
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
break;
case 'le':
$operator = 'lte';
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
break;
case 'gt':
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
break;
case 'ge':
$operator = 'gte';
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
break;
case 'in':
array_walk($value, function (&$v) {
if ($v == 'true()') {
$v = $qb->expr()->literal(TRUE);
} elseif ($v == 'false()') {
$v = $qb->expr()->literal(FALSE);
} elseif ($v == 'empty()') {
$v = $qb->expr()->literal('');
} else {
$v = $qb->expr()->literal($v);
}
});
$expr->add($qb->expr()->{$operator}($field, $value));
break;
case 'out':
$operator = 'notIn';
array_walk($value, function (&$v) {
if ($v == 'true()') {
$v = $qb->expr()->literal(TRUE);
} elseif ($v == 'false()') {
$v = $qb->expr()->literal(FALSE);
} elseif ($v == 'empty()') {
$v = $qb->expr()->literal('');
} else {
$v = $qb->expr()->literal($v);
}
});
$expr->add($qb->expr()->{$operator}($field, $value));
break;
default:
$value = $qb->expr()->literal($value);
$expr->add($qb->expr()->{$operator}($field, $value));
}
}
}
}
}
return $expr;
}
/**
* @param Composite $expressions
* @param $filterCondition
*
* @param QueryBuilder $queryBuilder
*
* @throws \Netdudes\DataSourceryBundle\DataSource\Driver\Doctrine\Exception\ColumnNotFoundException
* @throws \Exception
*/
protected function addExpressionsForFilterCondition(Composite $expressions, FilterCondition $filterCondition, QueryBuilder $queryBuilder)
{
// Build an unique token name for parameter substitution
$token = $this->buildUniqueToken($filterCondition, $queryBuilder);
$filterMethod = $filterCondition->getMethod();
// Add the filtering statement
$value = $filterCondition->getValue();
// Flag to not insert the parameter if the logic requires it
$ignoreParameter = false;
// Depending on the filter type, create a condition
$condition = $this->buildCondition($filterCondition, $token, $queryBuilder);
$expressions->add($condition);
// Modify the value if needed
if ($filterMethod == FilterCondition::METHOD_STRING_LIKE) {
$value = str_replace('*', '%', $filterCondition->getValue());
}
// Ignore value if needed
if ($filterMethod == FilterCondition::METHOD_IN && count($filterCondition->getValue()) <= 0 || $filterMethod == FilterCondition::METHOD_IS_NULL) {
$ignoreParameter = true;
}
// Insert the value substituting the token
if (!$ignoreParameter) {
$queryBuilder->setParameter($token, $value);
}
}
/**
* @param array $criteria
* @param Expr\Composite $expr
* @throws \InvalidArgumentException
*/
protected function populate(array $criteria, Expr\Composite $expr)
{
if ($this->isComparison($criteria)) {
$expr->add($this->normalizeComparison($criteria));
return;
}
foreach ($criteria as $operatorOrField => $comparisonOrValue) {
if (!$this->isOperator($operatorOrField)) {
if ($this->isComparison($comparisonOrValue)) {
$comparison = $this->normalizeComparison($comparisonOrValue);
} else {
if (!$this->isField($operatorOrField)) {
if (is_int($operatorOrField) && is_array($comparisonOrValue)) {
$this->populate($comparisonOrValue, $expr);
continue;
}
throw new \InvalidArgumentException(sprintf('Criteria format is invalid; "%s" is not a valid field for "%s" value', $operatorOrField, is_object($comparisonOrValue) ? get_class($comparisonOrValue) : print_r($comparisonOrValue, true)));
}
$operator = is_array($comparisonOrValue) ? static::IN : static::EQUAL;
$comparison = $this->{$operator}($operatorOrField, $comparisonOrValue);
}
$expr->add($comparison);
} elseif ($this->isComposite($operatorOrField)) {
$operator = $this->normalizeOperator($operatorOrField);
$composite = $this->{$operator}();
$this->populate($comparisonOrValue, $composite);
$expr->add($composite);
} else {
$operator = $this->normalizeOperator($operatorOrField);
$composite = $this->getDefaultComposite();
$this->populate($comparisonOrValue, $composite);
$expr->add($this->{$operator}($composite));
}
}
}
private function processFilter(ExpressionManager $expressionManager, Expr\Composite $compositeExpr, QueryBuilder $qb, DoctrineQueryBuilderParametersBinder $binder, Filter $filter)
{
$name = $filter->getProperty();
$fieldName = $expressionManager->getDqlPropertyName($name);
if (in_array($filter->getComparator(), array(Filter::COMPARATOR_IS_NULL, Filter::COMPARATOR_IS_NOT_NULL))) {
// these are sort of 'special case'
$compositeExpr->add($qb->expr()->{$filter->getComparator()}($fieldName));
} else {
$value = $filter->getValue();
$comparatorName = $filter->getComparator();
if (!$this->isUsefulInFilter($filter->getComparator(), $filter->getValue()) || !$this->isUsefulFilter($expressionManager, $name, $value)) {
return;
}
// when "IN" is used in conjunction with TO_MANY type of relation,
// then we will treat it in a special way and generate "MEMBER OF" queries
// instead
$isAdded = false;
if ($expressionManager->isAssociation($name)) {
$mapping = $expressionManager->getMapping($name);
if (in_array($comparatorName, array(Filter::COMPARATOR_IN, Filter::COMPARATOR_NOT_IN)) && in_array($mapping['type'], array(CMI::ONE_TO_MANY, CMI::MANY_TO_MANY))) {
$statements = array();
foreach ($value as $id) {
$statements[] = sprintf((Filter::COMPARATOR_NOT_IN == $comparatorName ? 'NOT ' : '') . '?%d MEMBER OF %s', $binder->getNextIndex(), $expressionManager->getDqlPropertyName($name));
$binder->bind($this->convertValue($expressionManager, $name, $id));
}
if (Filter::COMPARATOR_IN == $comparatorName) {
$compositeExpr->add(call_user_func_array(array($qb->expr(), 'orX'), $statements));
} else {
$compositeExpr->addMultiple($statements);
}
$isAdded = true;
}
}
if (!$isAdded) {
if (is_array($value) && count($value) != count($value, \COUNT_RECURSIVE)) {
// must be "OR-ed" ( multi-dimensional array )
$orStatements = array();
foreach ($value as $orFilter) {
if (!$this->isUsefulInFilter($orFilter['comparator'], $orFilter['value']) || !$this->isUsefulFilter($expressionManager, $name, $orFilter['value'])) {
continue;
}
if (in_array($orFilter['comparator'], array(Filter::COMPARATOR_IN, Filter::COMPARATOR_NOT_IN))) {
$orStatements[] = $qb->expr()->{$orFilter['comparator']}($fieldName);
} else {
$orStatements[] = $qb->expr()->{$orFilter['comparator']}($fieldName, '?' . $binder->getNextIndex());
}
$binder->bind($orFilter['value']);
}
$compositeExpr->add(call_user_func_array(array($qb->expr(), 'orX'), $orStatements));
} else {
$compositeExpr->add($qb->expr()->{$comparatorName}($fieldName, '?' . $binder->getNextIndex()));
$binder->bind($this->convertValue($expressionManager, $name, $value));
}
}
}
}