protected function foreignIDFilter($id = null) { if ($id === null) { $id = $this->getForeignID(); } // Apply relation filter $key = DataObject::getSchema()->sqlColumnForField($this->dataClass(), $this->getForeignKey()); if (is_array($id)) { return array("{$key} IN (" . DB::placeholders($id) . ")" => $id); } else { if ($id !== null) { return array($key => $id); } } return null; }
/** * Link this group set to a specific member. * * Recursively selects all groups applied to this member, as well as any * parent groups of any applied groups * * @param array|integer $id (optional) An ID or an array of IDs - if not provided, will use the current * ids as per getForeignID * @return array Condition In array(SQL => parameters format) */ public function foreignIDFilter($id = null) { if ($id === null) { $id = $this->getForeignID(); } // Find directly applied groups $manyManyFilter = parent::foreignIDFilter($id); $query = new SQLSelect('"Group_Members"."GroupID"', '"Group_Members"', $manyManyFilter); $groupIDs = $query->execute()->column(); // Get all ancestors, iteratively merging these into the master set $allGroupIDs = array(); while ($groupIDs) { $allGroupIDs = array_merge($allGroupIDs, $groupIDs); $groupIDs = DataObject::get("SilverStripe\\Security\\Group")->byIDs($groupIDs)->column("ParentID"); $groupIDs = array_filter($groupIDs); } // Add a filter to this DataList if (!empty($allGroupIDs)) { $allGroupIDsPlaceholders = DB::placeholders($allGroupIDs); return array("\"Group\".\"ID\" IN ({$allGroupIDsPlaceholders})" => $allGroupIDs); } else { return array('"Group"."ID"' => 0); } }
/** * 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(); if (empty($values)) { throw new \InvalidArgumentException("Cannot filter {$field} against an empty set"); } $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)); }
protected function foreignIDFilter($id = null) { if ($id === null) { $id = $this->getForeignID(); } // Apply relation filter $key = "\"{$this->joinTable}\".\"{$this->foreignKey}\""; if (is_array($id)) { return array("{$key} IN (" . DB::placeholders($id) . ")" => $id); } else { if ($id !== null) { return array($key => $id); } } }
/** * Return all of the groups that have one of the given permission codes * @param array|string $codes Either a single permission code, or an array of permission codes * @return SS_List The matching group objects */ public static function get_groups_by_permission($codes) { $codeParams = is_array($codes) ? $codes : array($codes); $codeClause = DB::placeholders($codeParams); // Via Roles are groups that have the permission via a role /** @skipUpgrade */ return Group::get()->where(array("\"PermissionRoleCode\".\"Code\" IN ({$codeClause}) OR \"Permission\".\"Code\" IN ({$codeClause})" => array_merge($codeParams, $codeParams)))->leftJoin('Permission', "\"Permission\".\"GroupID\" = \"Group\".\"ID\"")->leftJoin('Group_Roles', "\"Group_Roles\".\"GroupID\" = \"Group\".\"ID\"")->leftJoin('PermissionRole', "\"Group_Roles\".\"PermissionRoleID\" = \"PermissionRole\".\"ID\"")->leftJoin('PermissionRoleCode', "\"PermissionRoleCode\".\"RoleID\" = \"PermissionRole\".\"ID\""); }
/** * Safely query and return all pages queried * * @param array $ids * @return SS_List */ protected function getPages($ids) { // Check empty set if (empty($ids)) { return new ArrayList(); } $recordClass = $this->recordClass; // Bypass translatable filter if (class_exists('Translatable') && $recordClass::has_extension('Translatable')) { Translatable::disable_locale_filter(); } // Bypass versioned filter if ($recordClass::has_extension(Versioned::class)) { // Workaround for get_including_deleted not supporting byIDs filter very well // Ensure we select both stage / live records $pages = Versioned::get_including_deleted($recordClass, array('"RecordID" IN (' . DB::placeholders($ids) . ')' => $ids)); } else { $pages = DataObject::get($recordClass)->byIDs($ids); } if (class_exists('Translatable') && $recordClass::has_extension('Translatable')) { Translatable::enable_locale_filter(); } return $pages; }
/** * @param array $params Request params (unsanitized) */ public function __construct($params = null) { $this->ids = array(); $this->expanded = array(); $parents = array(); $q = $this->getQuery($params); $res = $q->execute(); if (!$res) { return; } // And keep a record of parents we don't need to get parents // of themselves, as well as IDs to mark foreach ($res as $row) { if ($row['ParentID']) { $parents[$row['ParentID']] = true; } $this->ids[$row['ID']] = true; } // We need to recurse up the tree, // finding ParentIDs for each ID until we run out of parents while (!empty($parents)) { $parentsClause = DB::placeholders($parents); $res = DB::prepared_query("SELECT \"ParentID\", \"ID\" FROM \"SiteTree\" WHERE \"ID\" in ({$parentsClause})", array_keys($parents)); $parents = array(); foreach ($res as $row) { if ($row['ParentID']) { $parents[$row['ParentID']] = true; } $this->ids[$row['ID']] = true; $this->expanded[$row['ID']] = true; } } }
/** * Ensure that the query is ready to execute. * * @param array|null $queriedColumns Any columns to filter the query by * @return SQLSelect The finalised sql query */ public function getFinalisedQuery($queriedColumns = null) { if (!$queriedColumns) { $queriedColumns = $this->queriedColumns; } if ($queriedColumns) { $queriedColumns = array_merge($queriedColumns, array('Created', 'LastEdited', 'ClassName')); } $schema = DataObject::getSchema(); $query = clone $this->query; $baseDataClass = $schema->baseDataClass($this->dataClass()); $baseIDColumn = $schema->sqlColumnForField($baseDataClass, 'ID'); $ancestorClasses = ClassInfo::ancestry($this->dataClass(), true); // Generate the list of tables to iterate over and the list of columns required // by any existing where clauses. This second step is skipped if we're fetching // the whole dataobject as any required columns will get selected regardless. if ($queriedColumns) { // Specifying certain columns allows joining of child tables $tableClasses = ClassInfo::dataClassesFor($this->dataClass); // Ensure that any filtered columns are included in the selected columns foreach ($query->getWhereParameterised($parameters) as $where) { // Check for any columns in the form '"Column" = ?' or '"Table"."Column"' = ? if (preg_match_all('/(?:"(?<table>[^"]+)"\\.)?"(?<column>[^"]+)"(?:[^\\.]|$)/', $where, $matches, PREG_SET_ORDER)) { foreach ($matches as $match) { $column = $match['column']; if (!in_array($column, $queriedColumns)) { $queriedColumns[] = $column; } } } } } else { $tableClasses = $ancestorClasses; } // Iterate over the tables and check what we need to select from them. If any selects are made (or the table is // required for a select) foreach ($tableClasses as $tableClass) { // Determine explicit columns to select $selectColumns = null; if ($queriedColumns) { // Restrict queried columns to that on the selected table $tableFields = DataObject::database_fields($tableClass); unset($tableFields['ID']); $selectColumns = array_intersect($queriedColumns, array_keys($tableFields)); } // If this is a subclass without any explicitly requested columns, omit this from the query if (!in_array($tableClass, $ancestorClasses) && empty($selectColumns)) { continue; } // Select necessary columns (unless an explicitly empty array) if ($selectColumns !== array()) { $this->selectColumnsFromTable($query, $tableClass, $selectColumns); } // Join if not the base table if ($tableClass !== $baseDataClass) { $tableName = $schema->tableName($tableClass); $query->addLeftJoin($tableName, "\"{$tableName}\".\"ID\" = {$baseIDColumn}", $tableName, 10); } } // Resolve colliding fields if ($this->collidingFields) { foreach ($this->collidingFields as $collisionField => $collisions) { $caseClauses = array(); foreach ($collisions as $collision) { if (preg_match('/^"(?<table>[^"]+)"\\./', $collision, $matches)) { $collisionTable = $matches['table']; $collisionClass = $schema->tableClass($collisionTable); if ($collisionClass) { $collisionClassColumn = $schema->sqlColumnForField($collisionClass, 'ClassName'); $collisionClasses = ClassInfo::subclassesFor($collisionClass); $collisionClassesSQL = implode(', ', Convert::raw2sql($collisionClasses, true)); $caseClauses[] = "WHEN {$collisionClassColumn} IN ({$collisionClassesSQL}) THEN {$collision}"; } } else { user_error("Bad collision item '{$collision}'", E_USER_WARNING); } } $query->selectField("CASE " . implode(" ", $caseClauses) . " ELSE NULL END", $collisionField); } } if ($this->filterByClassName) { // If querying the base class, don't bother filtering on class name if ($this->dataClass != $baseDataClass) { // Get the ClassName values to filter to $classNames = ClassInfo::subclassesFor($this->dataClass); $classNamesPlaceholders = DB::placeholders($classNames); $baseClassColumn = $schema->sqlColumnForField($baseDataClass, 'ClassName'); $query->addWhere(array("{$baseClassColumn} IN ({$classNamesPlaceholders})" => $classNames)); } } // Select ID $query->selectField($baseIDColumn, "ID"); // Select RecordClassName $baseClassColumn = $schema->sqlColumnForField($baseDataClass, 'ClassName'); $query->selectField("\n\t\t\tCASE WHEN {$baseClassColumn} IS NOT NULL THEN {$baseClassColumn}\n\t\t\tELSE " . Convert::raw2sql($baseDataClass, true) . " END", "RecordClassName"); // TODO: Versioned, Translatable, SiteTreeSubsites, etc, could probably be better implemented as subclasses // of DataQuery $obj = Injector::inst()->get($this->dataClass); $obj->extend('augmentSQL', $query, $this); $this->ensureSelectContainsOrderbyColumns($query); return $query; }
/** * Get a map of all members in the groups given that have CMS permissions * * If no groups are passed, all groups with CMS permissions will be used. * * @param array $groups Groups to consider or NULL to use all groups with * CMS permissions. * @return Map Returns a map of all members in the groups given that * have CMS permissions. */ public static function mapInCMSGroups($groups = null) { if (!$groups || $groups->Count() == 0) { $perms = array('ADMIN', 'CMS_ACCESS_AssetAdmin'); if (class_exists('SilverStripe\\CMS\\Controllers\\CMSMain')) { $cmsPerms = CMSMain::singleton()->providePermissions(); } else { $cmsPerms = LeftAndMain::singleton()->providePermissions(); } if (!empty($cmsPerms)) { $perms = array_unique(array_merge($perms, array_keys($cmsPerms))); } $permsClause = DB::placeholders($perms); /** @skipUpgrade */ $groups = Group::get()->innerJoin("Permission", '"Permission"."GroupID" = "Group"."ID"')->where(array("\"Permission\".\"Code\" IN ({$permsClause})" => $perms)); } $groupIDList = array(); if ($groups instanceof SS_List) { foreach ($groups as $group) { $groupIDList[] = $group->ID; } } elseif (is_array($groups)) { $groupIDList = $groups; } /** @skipUpgrade */ $members = Member::get()->innerJoin("Group_Members", '"Group_Members"."MemberID" = "Member"."ID"')->innerJoin("Group", '"Group"."ID" = "Group_Members"."GroupID"'); if ($groupIDList) { $groupClause = DB::placeholders($groupIDList); $members = $members->where(array("\"Group\".\"ID\" IN ({$groupClause})" => $groupIDList)); } return $members->sort('"Member"."Surname", "Member"."FirstName"')->map(); }
/** * Pre-populate the cache for Versioned::get_versionnumber_by_stage() for * a list of record IDs, for more efficient database querying. If $idList * is null, then every record will be pre-cached. * * @param string $class * @param string $stage * @param array $idList */ public static function prepopulate_versionnumber_cache($class, $stage, $idList = null) { if (!Config::inst()->get('SilverStripe\\ORM\\Versioning\\Versioned', 'prepopulate_versionnumber_cache')) { return; } $filter = ""; $parameters = array(); if ($idList) { // Validate the ID list foreach ($idList as $id) { if (!is_numeric($id)) { user_error("Bad ID passed to Versioned::prepopulate_versionnumber_cache() in \$idList: " . $id, E_USER_ERROR); } } $filter = 'WHERE "ID" IN (' . DB::placeholders($idList) . ')'; $parameters = $idList; } /** @var Versioned|DataObject $singleton */ $singleton = DataObject::singleton($class); $baseClass = $singleton->baseClass(); $baseTable = $singleton->baseTable(); $stageTable = $singleton->stageTable($baseTable, $stage); $versions = DB::prepared_query("SELECT \"ID\", \"Version\" FROM \"{$stageTable}\" {$filter}", $parameters)->map(); foreach ($versions as $id => $version) { self::$cache_versionnumber[$baseClass][$stage][$id] = $version; } }