/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { $ctrl = null; if (Controller::has_curr()) { $ctrl = Controller::curr(); } if (Subsite::$disable_subsite_filter) { return; } if ($dataQuery->getQueryParam('Subsite.filter') === false) { return; } if ($ctrl && get_class(Controller::curr()) == 'Security') { return; } // Don't run on delete queries, since they are always tied to // a specific ID. if ($query->getDelete()) { return; } // If you're querying by ID, ignore the sub-site - this is a bit ugly... // if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) { if (!$query->filtersOnID()) { if (Subsite::$force_subsite) { $subsiteID = Subsite::$force_subsite; } else { $subsiteID = (int) Subsite::currentSubsiteID(); } $froms = $query->getFrom(); $froms = array_keys($froms); $tableName = array_shift($froms); $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})"); } }
/** * Update any requests to limit the results to the current site */ function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { if (Subsite::$disable_subsite_filter) { return; } if ($dataQuery->getQueryParam('Subsite.filter') === false) { return; } // Don't run on delete queries, since they are always tied to // a specific ID. if ($query->getDelete()) { return; } // If you're querying by ID, ignore the sub-site - this is a bit ugly... // if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) { if (!$query->where || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->where[0])) { if (Subsite::$force_subsite) { $subsiteID = Subsite::$force_subsite; } else { /*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID; else */ $subsiteID = (int) Subsite::currentSubsiteID(); } // The foreach is an ugly way of getting the first key :-) foreach ($query->getFrom() as $tableName => $info) { // The tableName should be SiteTree or SiteTree_Live... if (strpos($tableName, $this->owner->ClassName) === false) { break; } $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})"); break; } } }
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Actives locales defined on a SiteConfig are there as a global setting if ($this->owner instanceof SiteConfig) { return; } // In admin, show everthing anyway if ($this->isAdminBackend()) { return; } // Find in set is only compatible with MySql $c = DB::getConn(); if (!$c instanceof MySQLDatabase) { return; } $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale(); $from = $query->getFrom(); $where = $query->getWhere(); $column = 'ActiveLocales'; $table = null; // Check on which table is the ActiveLocales field foreach ($from as $fromTable => $conditions) { if ($table === null) { $table = $fromTable; } $db = DataObject::custom_database_fields($fromTable); if ($db && isset($db[$column])) { $table = $fromTable; break; } } $identifier = "\"{$table}\".\"{$column}\""; $where[] = "{$identifier} IS NULL OR FIND_IN_SET ('{$locale}', {$identifier}) > 0"; $query->setWhere($where); }
/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL) { if (Subsite::$disable_subsite_filter) { return; } if ($dataQuery->getQueryParam('Subsite.filter') === false) { return; } // If you're querying by ID, ignore the sub-site - this is a bit ugly... // if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) { if ($query->filtersOnID()) { return; } if (Subsite::$force_subsite) { $subsiteID = Subsite::$force_subsite; } else { /*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID; else */ $subsiteID = (int) Subsite::currentSubsiteID(); } // The foreach is an ugly way of getting the first key :-) foreach ($query->getFrom() as $tableName => $info) { // The tableName should be SiteTree or SiteTree_Live... if (strpos($tableName, 'SiteTree') === false) { break; } $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})"); break; } }
/** * 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 \SQLQuery */ public function getQuery() { if (!$this->queryCache) { if ($this->dataClass) { $dataQuery = new \DataQuery($this->dataClass); if ($this->stage) { $dataQuery->setQueryParam('Versioned.mode', 'stage'); $dataQuery->setQueryParam('Versioned.stage', $this->stage); } $this->queryCache = $dataQuery->getFinalisedQuery(); } else { $this->queryCache = new \SQLQuery(); } if (is_array($this->queryModifiers)) { foreach ($this->queryModifiers as $queryModifier) { if ($queryModifier instanceof QueryModifierInterface) { $queryModifier->modify($this->queryCache, $this->data, $this); } elseif (is_callable($queryModifier)) { $queryModifier($this->queryCache, $this->data, $this); } } } } return $this->queryCache; }
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()) )); }
/** * @param SQLQuery $query * @param DataQuery $dataQuery */ function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { $baseTable = ClassInfo::baseDataClass($dataQuery->dataClass()); if (class_exists('Subsite')) { $currentSubsiteID = Subsite::currentSubsiteID(); $query->addWhere("\"{$baseTable}\".\"SubsiteID\" = '{$currentSubsiteID}'"); } }
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)); }
/** * Test the leftJoin() and innerJoin method of the DataQuery object */ function testJoins() { $dq = new DataQuery('Member'); $dq->innerJoin("Group_Members", "\"Group_Members\".\"MemberID\" = \"Member\".\"ID\""); $this->assertContains("INNER JOIN \"Group_Members\" ON \"Group_Members\".\"MemberID\" = \"Member\".\"ID\"", $dq->sql()); $dq = new DataQuery('Member'); $dq->leftJoin("Group_Members", "\"Group_Members\".\"MemberID\" = \"Member\".\"ID\""); $this->assertContains("LEFT JOIN \"Group_Members\" ON \"Group_Members\".\"MemberID\" = \"Member\".\"ID\"", $dq->sql()); }
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)); }
/** * @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); }
/** * 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); }
public function testSubgroupHandoff() { $dq = new DataQuery('DataQueryTest_A'); $subDq = $dq->disjunctiveGroup(); $orgDq = clone $dq; $subDq->sort('"DataQueryTest_A"."Name"'); $orgDq->sort('"DataQueryTest_A"."Name"'); $this->assertEquals($dq->sql(), $orgDq->sql()); $subDq->limit(5, 7); $orgDq->limit(5, 7); $this->assertEquals($dq->sql(), $orgDq->sql()); }
/** * @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(); $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)); }
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)); }
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))); } } } }
/** * 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)); } }
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.'); }
/** * *@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; }
/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Filters are disabled globally if (self::$disable) { return; } // Filters are disabled for this query if ($dataQuery->getQueryParam('SoftDeletable.filter') === false) { return; } // Don't run on delete queries, since they are always tied to a specific ID. if ($query->getDelete()) { return; } // Don't run if querying by ID if ($query->filtersOnID()) { return; } $froms = $query->getFrom(); $froms = array_keys($froms); $tableName = array_shift($froms); $query->addWhere("\"{$tableName}\".\"Deleted\" IS NULL"); }
/** * Looks for files used in system and create where clause which contains all ID's of files. * * @returns String where clause which will work as filter. */ public function getUnusedFilesListFilter() { $result = DB::query("SELECT DISTINCT \"FileID\" FROM \"SiteTree_ImageTracking\""); $usedFiles = array(); $where = ''; $classes = ClassInfo::subclassesFor('SiteTree'); if ($result->numRecords() > 0) { while ($nextResult = $result->next()) { $where .= $nextResult['FileID'] . ','; } } foreach ($classes as $className) { $query = new DataQuery($className); $ids = $query->execute()->column(); if (!count($ids)) { continue; } foreach (singleton($className)->hasOne() as $relName => $joinClass) { if ($joinClass == 'Image' || $joinClass == 'File') { $fieldName = $relName . 'ID'; $query = DataList::create($className)->where("{$fieldName} > 0"); $query->distinct = true; $query->select(array($fieldName)); $usedFiles = array_merge($usedFiles, $query->execute()->column()); } elseif ($joinClass == 'Folder') { // @todo } } } if ($usedFiles) { return "\"File\".\"ID\" NOT IN (" . implode(', ', $usedFiles) . ") AND (\"ClassName\" = 'File' OR \"ClassName\" = 'Image')"; } else { return "(\"ClassName\" = 'File' OR \"ClassName\" = 'Image')"; } return $where; // @todo - How? }
/** * 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); }
/** * 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']); } } } }