/**
* 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);
}
