/** * Applies the filter. * Builds the where clause with the given IDs and boolean values in * $this->value * * @param DataQuery $query Query to build where clause for * * @return DataQuery * * @author Sebastian Diel <*****@*****.**> * @since 25.06.2014 */ public function apply(DataQuery $query) { $result = false; $value = $this->getValue(); if (is_array($value) && count($value) > 0) { $this->model = $query->applyRelation($this->relation); $values = array(0 => array(), 1 => array()); foreach ($value as $ID => $boolean) { $operator = '!='; if ($boolean) { $operator = '='; } $values[$boolean][] = sprintf("%s %s '%s'", $this->getDbName(), $operator, Convert::raw2sql($ID)); } $negativeWhereClause = implode(' AND ', $values[0]); $positiveWhereClause = implode(' OR ', $values[1]); if (count($values[0]) > 0 && count($values[1]) > 0) { $where = sprintf('(%s) AND (%s)', $negativeWhereClause, $positiveWhereClause); } elseif (count($values[0]) > 0) { $where = $negativeWhereClause; } else { $where = $positiveWhereClause; } $result = $query->where($where); } return $result; }
public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); // hack // PREVIOUS $values = explode(',',$this->getValue()); $values = array(); if (is_string($this->getValue())) { $values = explode(',', $this->getValue()); } else { foreach ($this->getValue() as $v) { $values[] = $v; } } if (!$values) { return false; } for ($i = 0; $i < count($values); $i++) { if (!is_numeric($values[$i])) { // @todo Fix string replacement to only replace leading and tailing quotes $values[$i] = str_replace("'", '', $values[$i]); $values[$i] = Convert::raw2sql($values[$i]); } } $SQL_valueStr = "'" . implode("','", $values) . "'"; return $query->where(sprintf("%s IN (%s)", $this->getDbName(), $SQL_valueStr)); }
/** * @return $query */ public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where(sprintf( "%s > '%s'", $this->getDbName(), Convert::raw2sql($this->getDbFormattedValue()) )); }
public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $values = explode(',', $this->getValue()); foreach ($values as $value) { $matches[] = sprintf("%s LIKE '%s%%'", $this->getDbName(), Convert::raw2sql(str_replace("'", '', $value))); } return $query->where(implode(" OR ", $matches)); }
/** * Translates a Object relation name to a Database name and apply the relation join to * the query * * @param string $field * @return string */ public function getRelationName($field) { if(strpos($field,'.') === false) { return '"'.$field.'"'; } $relations = explode('.', $field); $fieldName = array_pop($relations); $relationModelName = $this->dataQuery->applyRelation($field); return '"'.$relationModelName.'"."'.$fieldName.'"'; }
protected function excludeMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $where = array(); $modifiers = $this->getModifiers(); foreach ($this->getValue() as $value) { $where[] = DB::getConn()->comparisonClause($this->getDbName(), '%' . Convert::raw2sql($value) . '%', false, true, $this->getCaseSensitive()); } return $query->where(implode(' AND ', $where)); }
/** * Applies a exclusion(inverse) filter to the query * Handles SQL escaping for both numeric and string values * * @param DataQuery $query * @return $this|DataQuery */ protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $value = $this->getDbFormattedValue(); if (is_numeric($value)) { $filter = sprintf("%s %s %s", $this->getDbName(), $this->getInverseOperator(), Convert::raw2sql($value)); } else { $filter = sprintf("%s %s '%s'", $this->getDbName(), $this->getInverseOperator(), Convert::raw2sql($value)); } return $query->where($filter); }
/** * @return $query */ public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $value = $this->getDbFormattedValue(); if (is_numeric($value)) { $filter = sprintf("%s < %s", $this->getDbName(), Convert::raw2sql($value)); } else { $filter = sprintf("%s < '%s'", $this->getDbName(), Convert::raw2sql($value)); } return $query->where($filter); }
public function testRelationReturn() { $dq = new DataQuery('DataQueryTest_C'); $this->assertEquals('DataQueryTest_A', $dq->applyRelation('TestA'), 'DataQuery::applyRelation should return the name of the related object.'); $this->assertEquals('DataQueryTest_A', $dq->applyRelation('TestAs'), 'DataQuery::applyRelation should return the name of the related object.'); $this->assertEquals('DataQueryTest_A', $dq->applyRelation('ManyTestAs'), 'DataQuery::applyRelation should return the name of the related object.'); $this->assertEquals('DataQueryTest_B', $dq->applyRelation('TestB'), 'DataQuery::applyRelation should return the name of the related object.'); $this->assertEquals('DataQueryTest_B', $dq->applyRelation('TestBs'), 'DataQuery::applyRelation should return the name of the related object.'); $this->assertEquals('DataQueryTest_B', $dq->applyRelation('ManyTestBs'), 'DataQuery::applyRelation should return the name of the related object.'); $newDQ = new DataQuery('DataQueryTest_E'); $this->assertEquals('DataQueryTest_A', $newDQ->applyRelation('TestA'), 'DataQuery::applyRelation should return the name of the related object.'); }
/** * Translates a Object relation name to a Database name and apply the relation join to * the query. Throws an InvalidArgumentException if the $field doesn't correspond to a relation * * @param string $field * @return string */ public function getRelationName($field) { if (!preg_match('/^[A-Z0-9._]+$/i', $field)) { throw new InvalidArgumentException("Bad field expression {$field}"); } if (strpos($field, '.') === false) { return '"' . $field . '"'; } $relations = explode('.', $field); $fieldName = array_pop($relations); $relationModelName = $this->dataQuery->applyRelation($field); return '"' . $relationModelName . '"."' . $fieldName . '"'; }
protected function excludeMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $values = $this->getValue(); $comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, false, true, $this->getCaseSensitive(), true); $parameters = array(); foreach ($values as $value) { $parameters[] = $this->getMatchPattern($value); } // Since query connective is ambiguous, use AND explicitly here $count = count($values); $predicate = implode(' AND ', array_fill(0, $count, $comparisonClause)); return $query->where(array($predicate => $parameters)); }
public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $where = array(); $comparison = DB::getConn() instanceof PostgreSQLDatabase ? 'ILIKE' : 'LIKE'; if (is_array($this->getValue())) { foreach ($this->getValue() as $value) { $where[] = sprintf("%s %s '%%%s%%'", $this->getDbName(), $comparison, Convert::raw2sql($value)); } } else { $where[] = sprintf("%s %s '%%%s%%'", $this->getDbName(), $comparison, Convert::raw2sql($this->getValue())); } return $query->where(implode(' OR ', $where)); }
/** * @param DataQuery $query * @return DataQuery */ protected function excludeMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $filters = array(); $ops = array('<', '>'); foreach ($this->getValue() as $i => $value) { if (is_numeric($value)) { $filters[] = sprintf("%s %s %s", $this->getDbName(), $ops[$i], Convert::raw2sql($value)); } else { $filters[] = sprintf("%s %s '%s'", $this->getDbName(), $ops[$i], Convert::raw2sql($value)); } } return $query->where(implode(' OR ', $filters)); }
public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $where = array(); if(is_array($this->getValue())) { foreach($this->getValue() as $value) { $where[]= sprintf("%s LIKE '%%%s%%'", $this->getDbName(), Convert::raw2sql($value)); } } else { $where[] = sprintf("%s LIKE '%%%s%%'", $this->getDbName(), Convert::raw2sql($this->getValue())); } return $query->where(implode(' OR ', $where)); }
/** * Translates a {@link Object} relation name to a Database name and apply * the relation join to the query. Throws an InvalidArgumentException if * the $field doesn't correspond to a relation. * * @throws InvalidArgumentException * @param string $field * * @return string */ public function getRelationName($field) { if (!preg_match('/^[A-Z0-9._]+$/i', $field)) { throw new InvalidArgumentException("Bad field expression {$field}"); } if (!$this->inAlterDataQueryCall) { Deprecation::notice('4.0', 'getRelationName is mutating, and must be called inside an alterDataQuery block'); } if (strpos($field, '.') === false) { return '"' . $field . '"'; } $relations = explode('.', $field); $fieldName = array_pop($relations); $relationModelName = $this->dataQuery->applyRelation($field); return '"' . $relationModelName . '"."' . $fieldName . '"'; }
/** * Applies an exact match (equals) on a field value against multiple * possible values. * * @return DataQuery */ protected function applyMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $modifiers = $this->getModifiers(); $values = array(); foreach ($this->getValue() as $value) { $values[] = Convert::raw2sql($value); } $CategoryModel = $this->model; $this->setModel("DataObjectAsPage"); $match = array(); foreach ($values as &$v) { $match[] = sprintf("%s IN (\n\t\t\t\t SELECT " . $query->dataClass() . "ID\n\t\t\t\t FROM `" . $query->dataClass() . "_" . $this->relation[0] . "`\n\t\t\t\t WHERE " . $CategoryModel . "ID = '%s'\n\t\t\t\t GROUP BY " . $query->dataClass() . "ID\n\t\t\t\t)", $this->getDbName(), $v); } $where = implode(' AND ', $match); return $query->where($where); }
/** * Excludes an exact match (equals) on a field value against multiple * possible values. * * @return DataQuery */ protected function excludeMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $modifiers = $this->getModifiers(); $values = array(); foreach ($this->getValue() as $value) { $values[] = Convert::raw2sql($value); } if (!in_array('case', $modifiers) && !in_array('nocase', $modifiers)) { $valueStr = "'" . implode("', '", $values) . "'"; return $query->where(sprintf('%s NOT IN (%s)', $this->getDbName(), $valueStr)); } else { foreach ($values as &$v) { $v = DB::getConn()->comparisonClause($this->getDbName(), $v, true, true, $this->getCaseSensitive()); } $where = implode(' OR ', $values); return $query->where($where); } }
/** * Excludes an exact match (equals) on a field value against multiple * possible values. * * @return DataQuery */ protected function excludeMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $caseSensitive = $this->getCaseSensitive(); $values = $this->getValue(); if ($caseSensitive === null) { // For queries using the default collation (no explicit case) we can use the WHERE .. NOT IN .. syntax, // providing simpler SQL than many WHERE .. AND .. fragments. $column = $this->getDbName(); $placeholders = DB::placeholders($values); return $query->where(array("{$column} NOT IN ({$placeholders})" => $values)); } else { // Generate reusable comparison clause $comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, true, true, $this->getCaseSensitive(), true); // Since query connective is ambiguous, use AND explicitly here $count = count($values); $predicate = implode(' AND ', array_fill(0, $count, $comparisonClause)); return $query->where(array($predicate => $values)); } }
public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where(sprintf("%s >= '%s' AND %s <= '%s'", $this->getDbName(), Convert::raw2sql($this->min), $this->getDbName(), Convert::raw2sql($this->max))); }
protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $predicate = sprintf('%1$s < ? OR %1$s > ?', $this->getDbName()); return $query->where(array($predicate => array($this->min, $this->max))); }
protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where(sprintf("%s < '%s' OR %s > '%s'", $this->getDbName(), Convert::raw2sql($this->min), $this->getDbName(), Convert::raw2sql($this->max))); }
protected function excludeOne(\DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where(sprintf("%s IS NOT NULL", $this->getDbName())); }
protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $predicate = sprintf("NOT MATCH (%s) AGAINST (?)", $this->getDbName()); return $query->where(array($predicate => $this->getValue())); }
/** * Apply filter query SQL to a search query * * @see SearchFilter::apply() * @return SQLQuery */ public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $value = $this->getValue(); if ($value) { $query->innerJoin('ProductCategory_Products', "\"ProductCategory_Products\".\"ProductID\" = \"SiteTree\".\"ID\""); $query->innerJoin('SiteTree_Live', "\"SiteTree_Live\".\"ID\" = \"ProductCategory_Products\".\"ProductCategoryID\""); $query->where("\"SiteTree_Live\".\"ID\" LIKE '%" . Convert::raw2sql($value) . "%'"); } return $query; }
/** * *@return SQLQuery **/ public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $value = $this->getValue(); if ($value && in_array($value, array(0, 1))) { $query->innerJoin($table = "Payment", $onPredicate = "\"Payment\".\"OrderID\" = \"Order\".\"ID\"", $tableAlias = null); } return $query; }
/** * Applies a exclusion(inverse) filter to the query * Handles SQL escaping for both numeric and string values * * @param DataQuery $query * @return $this|DataQuery */ protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $predicate = sprintf("%s %s ?", $this->getDbName(), $this->getInverseOperator()); return $query->where(array($predicate => $this->getDbFormattedValue())); }
/** * Applies a match on the trailing characters of a field value. * * @return unknown */ public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where(sprintf("%s %s '%%%s'", $this->getDbName(), DB::getConn() instanceof PostgreSQLDatabase ? 'ILIKE' : 'LIKE', Convert::raw2sql($this->getValue()))); }
/** * Applies matches for several values, either as inclusive or exclusive * * @param DataQuery $query * @param bool $inclusive True if this is inclusive, or false if exclusive * @return DataQuery */ protected function manyFilter(DataQuery $query, $inclusive) { $this->model = $query->applyRelation($this->relation); $caseSensitive = $this->getCaseSensitive(); // Check values for null $field = $this->getDbName(); $values = $this->getValue(); $hasNull = in_array(null, $values, true); if ($hasNull) { $values = array_filter($values, function ($value) { return $value !== null; }); } $connective = ''; if (empty($values)) { $predicate = ''; } elseif ($caseSensitive === null) { // For queries using the default collation (no explicit case) we can use the WHERE .. NOT IN .. syntax, // providing simpler SQL than many WHERE .. AND .. fragments. $column = $this->getDbName(); $placeholders = DB::placeholders($values); if ($inclusive) { $predicate = "{$column} IN ({$placeholders})"; } else { $predicate = "{$column} NOT IN ({$placeholders})"; } } else { // Generate reusable comparison clause $comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, true, !$inclusive, $this->getCaseSensitive(), true); $count = count($values); if ($count > 1) { $connective = $inclusive ? ' OR ' : ' AND '; $conditions = array_fill(0, $count, $comparisonClause); $predicate = implode($connective, $conditions); } else { $predicate = $comparisonClause; } } // Always check for null when doing exclusive checks (either AND IS NOT NULL / OR IS NULL) // or when including the null value explicitly (OR IS NULL) if ($hasNull || !$inclusive) { // If excluding values which don't include null, or including // values which include null, we should do an `OR IS NULL`. // Otherwise we are excluding values that do include null, so `AND IS NOT NULL`. // Simplified from (!$inclusive && !$hasNull) || ($inclusive && $hasNull); $isNull = !$hasNull || $inclusive; $nullCondition = DB::get_conn()->nullCheckClause($field, $isNull); // Determine merge strategy if (empty($predicate)) { $predicate = $nullCondition; } else { // Merge null condition with predicate if ($isNull) { $nullCondition = " OR {$nullCondition}"; } else { $nullCondition = " AND {$nullCondition}"; } // If current predicate connective doesn't match the same as the null connective // make sure to group the prior condition if ($connective && ($connective === ' OR ') !== $isNull) { $predicate = "({$predicate})"; } $predicate .= $nullCondition; } } return $query->where(array($predicate => $values)); }
/** * Applies a substring match on a field value. * * @return unknown */ public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where($this->getDbName() . " LIKE '" . Convert::raw2sql($this->getValue()) . "%'"); }
public function apply(DataQuery $query) { $this->model = $query->applyRelation($this->relation); return $query->where(sprintf("LOCATE('%s', %s) != 0", Convert::raw2sql($this->getValue()), $this->getDbName())); }