/**
* Exclude the list to not contain items with these characteristics
*
* @see SS_List::exclude()
* @example $list->exclude('Name', 'bob'); // exclude bob from list
* @example $list->exclude('Name', array('aziz', 'bob'); // exclude aziz and bob from list
* @example $list->exclude(array('Name'=>'bob, 'Age'=>21)); // exclude bob that has Age 21
* @example $list->exclude(array('Name'=>'bob, 'Age'=>array(21, 43))); // exclude bob with Age 21 or 43
* @example $list->exclude(array('Name'=>array('bob','phil'), 'Age'=>array(21, 43))); // bob age 21 or 43, phil age 21 or 43 would be excluded
*
* @todo extract the sql from this method 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 exclude()
{
$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 exclude()');
}
$SQL_Statements = array();
foreach ($whereArguments as $fieldName => $value) {
if ($fieldName == 'ID') {
$fieldName = sprintf('"%s"."ID"', ClassInfo::baseDataClass($this->dataClass));
} else {
$fieldName = '"' . Convert::raw2sql($fieldName) . '"';
}
if (is_array($value)) {
$SQL_Statements[] = $fieldName . ' NOT IN (\'' . implode('\',\'', Convert::raw2sql($value)) . '\')';
} else {
$SQL_Statements[] = $fieldName . ' != \'' . Convert::raw2sql($value) . '\'';
}
}
$this->dataQuery->whereAny($SQL_Statements);
return $this;
}
protected function applyMany(DataQuery $query)
{
$this->model = $query->applyRelation($this->relation);
$whereClause = array();
$comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, false, false, $this->getCaseSensitive(), true);
foreach ($this->getValue() as $value) {
$whereClause[] = array($comparisonClause => $this->getMatchPattern($value));
}
return $query->whereAny($whereClause);
}
/**
* 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);
$caseSensitive = $this->getCaseSensitive();
$values = $this->getValue();
if ($caseSensitive === null) {
// For queries using the default collation (no explicit case) we can use the WHERE .. IN .. syntax,
// providing simpler SQL than many WHERE .. OR .. fragments.
$column = $this->getDbName();
$placeholders = DB::placeholders($values);
return $query->where(array("{$column} IN ({$placeholders})" => $values));
} else {
$whereClause = array();
$comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, true, false, $caseSensitive, true);
foreach ($values as $value) {
$whereClause[] = array($comparisonClause => $value);
}
return $query->whereAny($whereClause);
}
}
/**
* Exclude the list to not contain items with these characteristics
*
* @see SS_List::exclude()
* @example $list->exclude('Name', 'bob'); // exclude bob from list
* @example $list->exclude('Name', array('aziz', 'bob'); // exclude aziz and bob from list
* @example $list->exclude(array('Name'=>'bob, 'Age'=>21)); // exclude bob that has Age 21
* @example $list->exclude(array('Name'=>'bob, 'Age'=>array(21, 43))); // exclude bob with Age 21 or 43
* @example $list->exclude(array('Name'=>array('bob','phil'), 'Age'=>array(21, 43))); // bob age 21 or 43, phil age 21 or 43 would be excluded
*
* @todo extract the sql from this method into a SQLGenerator class
*
* @return DataList
*/
public function exclude(){
$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('Arguments passed to exclude() is wrong');
}
$SQL_Statements = array();
foreach($whereArguments as $fieldName => $value) {
if(is_array($value)){
$SQL_Statements[] = ('"'.$fieldName.'" NOT IN (\''.implode('\',\'', Convert::raw2sql($value)).'\')');
} else {
$SQL_Statements[] = ('"'.$fieldName.'" != \''.Convert::raw2sql($value).'\'');
}
}
$this->dataQuery->whereAny($SQL_Statements);
return $this;
}
/**
* 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;
}
