/**
* 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()));
}
