/** * Return the LIMIT clause ready for inserting into a query. * * @param SQLSelect $query The expression object to build from * @param array $parameters Out parameter for the resulting query parameters * @return string The finalised limit SQL fragment */ public function buildLimitFragment(SQLSelect $query, array &$parameters) { $nl = $this->getSeparator(); // Ensure limit is given $limit = $query->getLimit(); if (empty($limit)) { return ''; } // For literal values return this as the limit SQL if (!is_array($limit)) { return "{$nl}LIMIT {$limit}"; } // Assert that the array version provides the 'limit' key if (!array_key_exists('limit', $limit) || $limit['limit'] !== null && !is_numeric($limit['limit'])) { throw new InvalidArgumentException('MySQLQueryBuilder::buildLimitSQL(): Wrong format for $limit: ' . var_export($limit, true)); } if ($limit['limit'] === null) { $limit['limit'] = self::MAX_ROWS; } // Format the array limit, given an optional start key $clause = "{$nl}LIMIT {$limit['limit']}"; if (isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) { $clause .= " OFFSET {$limit['start']}"; } return $clause; }
public function conditionSQL(&$parameters) { $parameters = array(); // Ignore empty conditions $where = $this->whereQuery->getWhere(); if (empty($where)) { return null; } // Allow database to manage joining of conditions $sql = DB::get_conn()->getQueryBuilder()->buildWhereFragment($this->whereQuery, $parameters); return preg_replace('/^\\s*WHERE\\s*/i', '', $sql); }
/** * 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); } }
/** * Sets the page length, page start and total items from a query object's * limit, offset and unlimited count. The query MUST have a limit clause. * * @param SQLSelect $query * @return $this */ public function setPaginationFromQuery(SQLSelect $query) { if ($limit = $query->getLimit()) { $this->setPageLength($limit['limit']); $this->setPageStart($limit['start']); $this->setTotalItems($query->unlimitedRowCount()); } return $this; }
/** * Returns true if the member is allowed to do the given action. * See {@link extendedCan()} for a more versatile tri-state permission control. * * @param string $perm The permission to be checked, such as 'View'. * @param Member $member The member whose permissions need checking. Defaults to the currently logged * in user. * @param array $context Additional $context to pass to extendedCan() * * @return boolean True if the the member is allowed to do the given action */ public function can($perm, $member = null, $context = array()) { if (!isset($member)) { $member = Member::currentUser(); } if (Permission::checkMember($member, "ADMIN")) { return true; } if ($this->getSchema()->manyManyComponent(static::class, 'Can' . $perm)) { if ($this->ParentID && $this->SecurityType == 'Inherit') { if (!($p = $this->Parent)) { return false; } return $this->Parent->can($perm, $member); } else { $permissionCache = $this->uninherited('permissionCache'); $memberID = $member ? $member->ID : 'none'; if (!isset($permissionCache[$memberID][$perm])) { if ($member->ID) { $groups = $member->Groups(); } $groupList = implode(', ', $groups->column("ID")); // TODO Fix relation table hardcoding $query = new SQLSelect("\"Page_Can{$perm}\".PageID", array("\"Page_Can{$perm}\""), "GroupID IN ({$groupList})"); $permissionCache[$memberID][$perm] = $query->execute()->column(); if ($perm == "View") { // TODO Fix relation table hardcoding $query = new SQLSelect("\"SiteTree\".\"ID\"", array("\"SiteTree\"", "LEFT JOIN \"Page_CanView\" ON \"Page_CanView\".\"PageID\" = \"SiteTree\".\"ID\""), "\"Page_CanView\".\"PageID\" IS NULL"); $unsecuredPages = $query->execute()->column(); if ($permissionCache[$memberID][$perm]) { $permissionCache[$memberID][$perm] = array_merge($permissionCache[$memberID][$perm], $unsecuredPages); } else { $permissionCache[$memberID][$perm] = $unsecuredPages; } } Config::inst()->update($this->class, 'permissionCache', $permissionCache); } if ($permissionCache[$memberID][$perm]) { return in_array($this->ID, $permissionCache[$memberID][$perm]); } } } else { return parent::can($perm, $member); } }
/** * Return the number of rows in this query if the limit were removed. Useful in paged data sets. * * @param string $column * @return int */ public function unlimitedRowCount($column = null) { // we can't clear the select if we're relying on its output by a HAVING clause if (count($this->having)) { $records = $this->execute(); return $records->numRecords(); } $clone = clone $this; $clone->limit = null; $clone->orderby = null; // Choose a default column if ($column == null) { if ($this->groupby) { // @todo Test case required here $countQuery = new SQLSelect(); $countQuery->setSelect("count(*)"); $countQuery->setFrom(array('(' . $clone->sql($innerParameters) . ') all_distinct')); $sql = $countQuery->sql($parameters); // $parameters should be empty $result = DB::prepared_query($sql, $innerParameters); return (int) $result->value(); } else { $clone->setSelect(array("count(*)")); } } else { $clone->setSelect(array("count({$column})")); } $clone->setGroupBy(array()); return (int) $clone->execute()->value(); }
/** * Find the extra field data for a single row of the relationship join * table, given the known child ID. * * @param string $componentName The name of the component * @param int $itemID The ID of the child for the relationship * * @return array Map of fieldName => fieldValue */ public function getExtraData($componentName, $itemID) { $result = array(); // Skip if no extrafields or unsaved record if (empty($this->extraFields) || empty($itemID)) { return $result; } if (!is_numeric($itemID)) { user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR); } $cleanExtraFields = array(); foreach ($this->extraFields as $fieldName => $dbFieldSpec) { $cleanExtraFields[] = "\"{$fieldName}\""; } $query = new SQLSelect($cleanExtraFields, "\"{$this->joinTable}\""); $filter = $this->foreignIDWriteFilter($this->getForeignID()); if ($filter) { $query->setWhere($filter); } else { user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING); } $query->addWhere(array("\"{$this->joinTable}\".\"{$this->localKey}\"" => $itemID)); $queryResult = $query->execute()->current(); if ($queryResult) { foreach ($queryResult as $fieldName => $value) { $result[$fieldName] = $value; } } return $result; }
public function testParameterisedLeftJoins() { $query = new SQLSelect(); $query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"')); $query->setFrom('"SQLSelectTest_DO"'); $query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%')); $query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00')); $this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count" FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" = "SubSelect"."Title" WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters)); $this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters); $query->execute(); }
/** * Set foreign keys of has_many objects to 0 where those objects were * disowned as a result of a partial publish / unpublish. * I.e. this object and its owned objects were recently written to $targetStage, * but deleted objects were not. * * Note that this operation does not create any new Versions * * @param string $sourceStage Objects in this stage will not be unlinked. * @param string $targetStage Objects which exist in this stage but not $sourceStage * will be unlinked. */ public function unlinkDisownedObjects($sourceStage, $targetStage) { $owner = $this->owner; // after publishing, objects which used to be owned need to be // dis-connected from this object (set ForeignKeyID = 0) $owns = $owner->config()->owns; $hasMany = $owner->config()->has_many; if (empty($owns) || empty($hasMany)) { return; } $ownedHasMany = array_intersect($owns, array_keys($hasMany)); foreach ($ownedHasMany as $relationship) { // Find metadata on relationship $joinClass = $owner->hasManyComponent($relationship); $joinField = $owner->getRemoteJoinField($relationship, 'has_many', $polymorphic); $idField = $polymorphic ? "{$joinField}ID" : $joinField; $joinTable = DataObject::getSchema()->tableForField($joinClass, $idField); // Generate update query which will unlink disowned objects $targetTable = $this->stageTable($joinTable, $targetStage); $disowned = new SQLUpdate("\"{$targetTable}\""); $disowned->assign("\"{$idField}\"", 0); $disowned->addWhere(array("\"{$targetTable}\".\"{$idField}\"" => $owner->ID)); // Build exclusion list (items to owned objects we need to keep) $sourceTable = $this->stageTable($joinTable, $sourceStage); $owned = new SQLSelect("\"{$sourceTable}\".\"ID\"", "\"{$sourceTable}\""); $owned->addWhere(array("\"{$sourceTable}\".\"{$idField}\"" => $owner->ID)); // Apply class condition if querying on polymorphic has_one if ($polymorphic) { $disowned->assign("\"{$joinField}Class\"", null); $disowned->addWhere(array("\"{$targetTable}\".\"{$joinField}Class\"" => get_class($owner))); $owned->addWhere(array("\"{$sourceTable}\".\"{$joinField}Class\"" => get_class($owner))); } // Merge queries and perform unlink $ownedSQL = $owned->sql($ownedParams); $disowned->addWhere(array("\"{$targetTable}\".\"ID\" NOT IN ({$ownedSQL})" => $ownedParams)); $owner->extend('updateDisownershipQuery', $disowned, $sourceStage, $targetStage, $relationship); $disowned->execute(); } }
/** * Invoked after getFinalisedQuery() * * @param DataQuery $dataQuery * @param array $queriedColumns * @param SQLSelect $sqlQuery */ public function afterGetFinalisedQuery(DataQuery $dataQuery, $queriedColumns = [], SQLSelect $sqlQuery) { // Inject final replacement after manipulation has been performed on the base dataquery $joinTableSQL = $dataQuery->getQueryParam('Foreign.JoinTableSQL'); if ($joinTableSQL) { $sqlQuery->replaceText('SELECT $$_SUBQUERY_$$', $joinTableSQL); $dataQuery->setQueryParam('Foreign.JoinTableSQL', null); } }
/** * Select the given field expressions. * * @param $fieldExpression String The field to select (escaped SQL statement) * @param $alias String The alias of that field (escaped SQL statement) */ public function selectField($fieldExpression, $alias = null) { $this->query->selectField($fieldExpression, $alias); }