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));
}
/**
* Modify this DataList, adding a filter
*/
public function addFilter($filterArray)
{
$SQL_Statements = array();
foreach ($filterArray as $field => $value) {
if (is_array($value)) {
$customQuery = 'IN (\'' . implode('\',\'', Convert::raw2sql($value)) . '\')';
} else {
$customQuery = '= \'' . Convert::raw2sql($value) . '\'';
}
if (stristr($field, ':')) {
$fieldArgs = explode(':', $field);
$field = array_shift($fieldArgs);
foreach ($fieldArgs as $fieldArg) {
$comparisor = $this->applyFilterContext($field, $fieldArg, $value);
}
} else {
if ($field == 'ID') {
$field = sprintf('"%s"."ID"', ClassInfo::baseDataClass($this->dataClass));
} else {
$field = '"' . Convert::raw2sql($field) . '"';
}
$SQL_Statements[] = $field . ' ' . $customQuery;
}
}
if (count($SQL_Statements)) {
foreach ($SQL_Statements as $SQL_Statement) {
$this->dataQuery->where($SQL_Statement);
}
}
return $this;
}
/**
* Filter the list to include items with these charactaristics
*
* @see SS_List::filter()
*
* @example $list->filter('Name', 'bob'); // only bob in the list
* @example $list->filter('Name', array('aziz', 'bob'); // aziz and bob in list
* @example $list->filter(array('Name'=>'bob, 'Age'=>21)); // bob with the age 21
* @example $list->filter(array('Name'=>'bob, 'Age'=>array(21, 43))); // bob with the Age 21 or 43
* @example $list->filter(array('Name'=>array('aziz','bob'), 'Age'=>array(21, 43))); // aziz with the age 21 or 43 and bob with the Age 21 or 43
*
* @todo extract the sql from $customQuery into a SQLGenerator class
*
* @param string|array Escaped SQL statement. If passed as array, all keys and values are assumed to be escaped.
* @return DataList
*/
public function filter()
{
$numberFuncArgs = count(func_get_args());
$whereArguments = array();
if ($numberFuncArgs == 1 && is_array(func_get_arg(0))) {
$whereArguments = func_get_arg(0);
} elseif ($numberFuncArgs == 2) {
$whereArguments[func_get_arg(0)] = func_get_arg(1);
} else {
throw new InvalidArgumentException('Incorrect number of arguments passed to filter()');
}
$SQL_Statements = array();
foreach ($whereArguments as $field => $value) {
if (is_array($value)) {
$customQuery = 'IN (\'' . implode('\',\'', Convert::raw2sql($value)) . '\')';
} else {
$customQuery = '= \'' . Convert::raw2sql($value) . '\'';
}
if (stristr($field, ':')) {
$fieldArgs = explode(':', $field);
$field = array_shift($fieldArgs);
foreach ($fieldArgs as $fieldArg) {
$comparisor = $this->applyFilterContext($field, $fieldArg, $value);
}
} else {
$SQL_Statements[] = '"' . Convert::raw2sql($field) . '" ' . $customQuery;
}
}
if (count($SQL_Statements)) {
foreach ($SQL_Statements as $SQL_Statement) {
$this->dataQuery->where($SQL_Statement);
}
}
return $this;
}
/**
* 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) {
$query->where(sprintf(
"MATCH (%s) AGAINST ('%s')",
$this->getDbName(),
Convert::raw2sql($this->getValue())
));
return $query;
}
/**
* @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)
{
if (!isset($this->min) || !isset($this->max)) {
$this->findMinMax();
}
if ($this->min && $this->max) {
$query->where(sprintf("%s >= '%s' AND %s <= '%s'", $this->getDbName(), Convert::raw2sql($this->min), $this->getDbName(), Convert::raw2sql($this->max)));
} else {
if ($this->min) {
$query->where(sprintf("%s >= '%s'", $this->getDbName(), Convert::raw2sql($this->min)));
} else {
if ($this->max) {
$query->where(sprintf("%s <= '%s'", $this->getDbName(), Convert::raw2sql($this->max)));
}
}
}
}
function apply(DataQuery $query) {
$query->where(sprintf(
"%s >= %s AND %s <= %s",
$this->getDbName(),
Convert::raw2sql($this->min),
$this->getDbName(),
Convert::raw2sql($this->max)
));
}
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));
}
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 testNestedGroups()
{
$dq = new DataQuery('DataQueryTest_A');
$dq->where('DataQueryTest_A.ID = 2');
$subDq = $dq->disjunctiveGroup();
$subDq->where('DataQueryTest_A.Name = \'John\'');
$subSubDq = $subDq->conjunctiveGroup();
$subSubDq->where('DataQueryTest_A.Age = 18');
$subSubDq->where('DataQueryTest_A.Age = 50');
$subDq->where('DataQueryTest_A.Name = \'Bob\'');
$this->assertContains("WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') OR ((DataQueryTest_A.Age = 18) " . "AND (DataQueryTest_A.Age = 50)) OR (DataQueryTest_A.Name = 'Bob'))", $dq->sql());
}
/**
*
*@return SQLQuery
**/
public function applyOne(DataQuery $query)
{
//$this->model = $query->applyRelation($this->relation);
$value = $this->getValue();
$date = new Date();
$date->setValue($value);
$distanceFromToday = time() - strtotime($value);
$maxDays = round($distanceFromToday / ($this->divider * 2 * 86400)) + 1;
$formattedDate = $date->format("Y-m-d");
// changed for PostgreSQL compatability
// NOTE - we may wish to add DATEDIFF function to PostgreSQL schema, it's just that this would be the FIRST function added for SilverStripe
// default is MySQL DATEDIFF() function - broken for others, each database conn type supported must be checked for!
$db = DB::getConn();
if ($db instanceof PostgreSQLDatabase) {
// don't know whether functions should be used, hence the following code using an interval cast to an integer
$query->where("(\"EcommercePayment\".\"Created\"::date - '{$formattedDate}'::date)::integer > -" . $maxDays . " AND (\"EcommercePayment\".\"Created\"::date - '{$formattedDate}'::date)::integer < " . $maxDays);
} else {
// default is MySQL DATEDIFF() function - broken for others, each database conn type supported must be checked for!
$query->where("(DATEDIFF(\"EcommercePayment\".\"Created\", '{$formattedDate}') > -" . $maxDays . " AND DATEDIFF(\"EcommercePayment\".\"Created\", '{$formattedDate}') < " . $maxDays . ")");
}
return $query;
}
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));
}
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();
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));
}
/**
* 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);
}
}
/**
* Loads all the stub fields than an initial lazy load didn't load fully.
*
* @param tableClass Base table to load the values from. Others are joined as required.
*/
protected function loadLazyFields($tableClass = null)
{
// Smarter way to work out the tableClass? Should the functionality in toMap and getField be moved into here?
if (!$tableClass) {
$tableClass = $this->ClassName;
}
$dataQuery = new DataQuery($tableClass);
// TableField sets the record ID to "new" on new row data, so don't try doing anything in that case
if (!is_numeric($this->record['ID'])) {
return false;
}
$dataQuery->where("\"{$tableClass}\".\"ID\" = {$this->record['ID']}")->limit(1);
$columns = array();
// Add SQL for fields, both simple & multi-value
// TODO: This is copy & pasted from buildSQL(), it could be moved into a method
$databaseFields = self::database_fields($tableClass);
if ($databaseFields) {
foreach ($databaseFields as $k => $v) {
if (!isset($this->record[$k]) || $this->record[$k] === null) {
$columns[] = $k;
}
}
}
if ($columns) {
$query = $dataQuery->query();
// eh?
$this->extend('augmentSQL', $query, $dataQuery);
$dataQuery->setQueriedColumns($columns);
$newData = $dataQuery->execute()->record();
// Load the data into record
if ($newData) {
foreach ($newData as $k => $v) {
if (in_array($k, $columns)) {
$this->record[$k] = $v;
$this->original[$k] = $v;
unset($this->record[$k . '_Lazy']);
}
}
// No data means that the query returned nothing; assign 'null' to all the requested fields
} else {
foreach ($columns as $k) {
$this->record[$k] = null;
$this->original[$k] = null;
unset($this->record[$k . '_Lazy']);
}
}
}
}
/**
* 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 __construct(DataQuery $base, $connective)
{
$this->dataClass = $base->dataClass;
$this->query = $base->query;
$this->whereQuery = new SQLQuery();
$this->whereQuery->setConnective($connective);
$base->where($this);
}
/**
* Loads all the stub fields that an initial lazy load didn't load fully.
*
* @param tableClass Base table to load the values from. Others are joined as required.
* Not specifying a tableClass will load all lazy fields from all tables.
*/
protected function loadLazyFields($tableClass = null)
{
if (!$tableClass) {
$loaded = array();
foreach ($this->record as $key => $value) {
if (strlen($key) > 5 && substr($key, -5) == '_Lazy' && !array_key_exists($value, $loaded)) {
$this->loadLazyFields($value);
$loaded[$value] = $value;
}
}
return;
}
$dataQuery = new DataQuery($tableClass);
// Reset query parameter context to that of this DataObject
if ($params = $this->getSourceQueryParams()) {
foreach ($params as $key => $value) {
$dataQuery->setQueryParam($key, $value);
}
}
// TableField sets the record ID to "new" on new row data, so don't try doing anything in that case
if (!is_numeric($this->record['ID'])) {
return false;
}
// Limit query to the current record, unless it has the Versioned extension,
// in which case it requires special handling through augmentLoadLazyFields()
if (!$this->hasExtension('Versioned')) {
$dataQuery->where("\"{$tableClass}\".\"ID\" = {$this->record['ID']}")->limit(1);
}
$columns = array();
// Add SQL for fields, both simple & multi-value
// TODO: This is copy & pasted from buildSQL(), it could be moved into a method
$databaseFields = self::database_fields($tableClass);
if ($databaseFields) {
foreach ($databaseFields as $k => $v) {
if (!isset($this->record[$k]) || $this->record[$k] === null) {
$columns[] = $k;
}
}
}
if ($columns) {
$query = $dataQuery->query();
$this->extend('augmentLoadLazyFields', $query, $dataQuery, $this);
$this->extend('augmentSQL', $query, $dataQuery);
$dataQuery->setQueriedColumns($columns);
$newData = $dataQuery->execute()->record();
// Load the data into record
if ($newData) {
foreach ($newData as $k => $v) {
if (in_array($k, $columns)) {
$this->record[$k] = $v;
$this->original[$k] = $v;
unset($this->record[$k . '_Lazy']);
}
}
// No data means that the query returned nothing; assign 'null' to all the requested fields
} else {
foreach ($columns as $k) {
$this->record[$k] = null;
$this->original[$k] = null;
unset($this->record[$k . '_Lazy']);
}
}
}
}
/**
* Apply filter query SQL to a search query
* Date range filtering between min and max values
*
* @param DataQuery $query The query object
*
* @return void
*
* @return void
*
* @author Sebastian Diel <*****@*****.**>,
* Sascha Koehler <*****@*****.**>
* @since 25.06.2014
*/
public function apply(DataQuery $query)
{
$this->initValue();
$min = Convert::raw2sql($this->min) . ' 00:00:00';
$max = Convert::raw2sql($this->max) . ' 23:59:59';
if ($this->min && $this->max) {
$query->where(sprintf("%s >= STR_TO_DATE('%s', '%%d.%%m.%%Y') AND %s <= STR_TO_DATE('%s', '%%d.%%m.%%Y %%H:%%i:%%s')", 'SilvercartOrder.Created', $min, 'SilvercartOrder.Created', $max));
} else {
if ($this->min) {
$query->where(sprintf("DATEDIFF(%s, STR_TO_DATE('%s', '%%d.%%m.%%Y')) = 0", 'SilvercartOrder.Created', $min));
}
}
}
/**
* Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed
* in the search.
*
* @return Array
*/
public function pagesIncluded()
{
$sng = singleton('SiteTree');
$ids = array();
$query = new DataQuery('SiteTree');
$query->setQueriedColumns(array('ID', 'ParentID'));
foreach ($this->params as $name => $val) {
$SQL_val = Convert::raw2sql($val);
switch ($name) {
case 'Term':
$query->whereAny(array("\"URLSegment\" LIKE '%{$SQL_val}%'", "\"Title\" LIKE '%{$SQL_val}%'", "\"MenuTitle\" LIKE '%{$SQL_val}%'", "\"Content\" LIKE '%{$SQL_val}%'"));
break;
case 'LastEditedFrom':
$fromDate = new DateField(null, null, $SQL_val);
$query->where("\"LastEdited\" >= '{$fromDate->dataValue()}'");
break;
case 'LastEditedTo':
$toDate = new DateField(null, null, $SQL_val);
$query->where("\"LastEdited\" <= '{$toDate->dataValue()}'");
break;
case 'ClassName':
if ($val && $val != 'All') {
$query->where("\"ClassName\" = '{$SQL_val}'");
}
break;
default:
if (!empty($val) && $sng->hasDatabaseField($name)) {
$filter = $sng->dbObject($name)->defaultSearchFilter();
$filter->setValue($val);
$filter->apply($query);
}
}
}
foreach ($query->execute() as $row) {
$ids[] = array('ID' => $row['ID'], 'ParentID' => $row['ParentID']);
}
return $ids;
}
/**
* 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())));
}
/**
*
*@return SQLQuery
**/
public function apply(DataQuery $query)
{
$this->model = $query->applyRelation($this->relation);
$value = $this->getValue();
if ($value == 1) {
$query->where("\"CancelledByID\" IS NOT NULL AND \"CancelledByID\" > 0");
}
return $query;
}
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)));
}
public function testComparisonClauseTextCaseSensitive()
{
DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyString\") VALUES ('HelloWorld')");
$query = new DataQuery('DataQueryTest_F');
$query->where(DB::get_conn()->comparisonClause('"MyString"', 'HelloWorld', false, false, true));
$this->assertGreaterThan(0, $query->count(), "Couldn't find MyString");
$query2 = new DataQuery('DataQueryTest_F');
$query2->where(DB::get_conn()->comparisonClause('"MyString"', 'helloworld', false, false, true));
$this->assertEquals(0, $query2->count(), "Found mystring. Shouldn't be able too.");
$this->resetDBSchema(true);
}
