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;
}
}
