/**
* @param QueryObjectModel $qom
*
* @return string
*/
public function walkQOMQuery(QueryObjectModel $qom)
{
$source = $qom->getSource();
$selectors = $this->validateSource($source);
$sourceSql = " " . $this->walkSource($source);
$constraintSql = '';
if ($constraint = $qom->getConstraint()) {
$constraintSql = " AND " . $this->walkConstraint($constraint);
}
$orderingSql = '';
if ($orderings = $qom->getOrderings()) {
$orderingSql = " " . $this->walkOrderings($orderings);
}
$sql = "SELECT " . $this->getColumns($qom);
$sql .= $sourceSql;
$sql .= $constraintSql;
$sql .= $orderingSql;
$limit = $qom->getLimit();
$offset = $qom->getOffset();
if (null !== $offset && null == $limit && ($this->platform instanceof MySqlPlatform || $this->platform instanceof SqlitePlatform)) {
$limit = PHP_INT_MAX;
}
$sql = $this->platform->modifyLimitQuery($sql, $limit, $offset);
return array($selectors, $this->alias, $sql);
}
/**
* Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT.
* This method is for platforms which DO NOT support ROW_NUMBER.
*
* @param SelectStatement $AST
* @param bool $addMissingItemsFromOrderByToSelect
*
* @return string
*
* @throws \RuntimeException
*/
public function walkSelectStatementWithoutRowNumber(SelectStatement $AST, $addMissingItemsFromOrderByToSelect = true)
{
// We don't want to call this recursively!
if ($AST->orderByClause instanceof OrderByClause && $addMissingItemsFromOrderByToSelect) {
// In the case of ordering a query by columns from joined tables, we
// must add those columns to the select clause of the query BEFORE
// the SQL is generated.
$this->addMissingItemsFromOrderByToSelect($AST);
}
// Remove order by clause from the inner query
// It will be re-appended in the outer select generated by this method
$orderByClause = $AST->orderByClause;
$AST->orderByClause = null;
$innerSql = $this->getInnerSQL($AST);
$sqlIdentifier = $this->getSQLIdentifier($AST);
// Build the counter query
$sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql);
// http://www.doctrine-project.org/jira/browse/DDC-1958
$sql = $this->preserveSqlOrdering($sqlIdentifier, $innerSql, $sql, $orderByClause);
// Apply the limit and offset.
$sql = $this->platform->modifyLimitQuery($sql, $this->maxResults, $this->firstResult);
// Add the columns to the ResultSetMapping. It's not really nice but
// it works. Preferably I'd clear the RSM or simply create a new one
// but that is not possible from inside the output walker, so we dirty
// up the one we have.
foreach ($sqlIdentifier as $property => $alias) {
$this->rsm->addScalarResult($alias, $property);
}
// Restore orderByClause
$AST->orderByClause = $orderByClause;
return $sql;
}
/**
* Return a list of all revisions.
*
* @param int $limit
* @param int $offset
* @return Revision[]
*/
public function findRevisionHistory($limit = 20, $offset = 0)
{
$query = $this->platform->modifyLimitQuery("SELECT * FROM " . $this->config->getRevisionTableName() . " ORDER BY id DESC", $limit, $offset);
$revisionsData = $this->em->getConnection()->fetchAll($query);
$revisions = array();
foreach ($revisionsData as $row) {
$revisions[] = new Revision($row['id'], \DateTime::createFromFormat($this->platform->getDateTimeFormatString(), $row['timestamp']), $row['username']);
}
return $revisions;
}
/**
* Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT
*
* @param SelectStatement $AST
* @return string
*/
public function walkSelectStatement(SelectStatement $AST)
{
$innerSql = parent::walkSelectStatement($AST);
// Find out the SQL alias of the identifier column of the root entity
// It may be possible to make this work with multiple root entities but that
// would probably require issuing multiple queries or doing a UNION SELECT
// so for now, It's not supported.
// Get the root entity and alias from the AST fromClause
$from = $AST->fromClause->identificationVariableDeclarations;
if (count($from) !== 1) {
throw new \RuntimeException("Cannot count query which selects two FROM components, cannot make distinction");
}
$rootAlias = $from[0]->rangeVariableDeclaration->aliasIdentificationVariable;
$rootClass = $this->queryComponents[$rootAlias]['metadata'];
$rootIdentifier = $rootClass->identifier;
// For every identifier, find out the SQL alias by combing through the ResultSetMapping
$sqlIdentifier = array();
foreach ($rootIdentifier as $property) {
if (isset($rootClass->fieldMappings[$property])) {
foreach (array_keys($this->rsm->fieldMappings, $property) as $alias) {
if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
$sqlIdentifier[$property] = $alias;
}
}
}
if (isset($rootClass->associationMappings[$property])) {
$joinColumn = $rootClass->associationMappings[$property]['joinColumns'][0]['name'];
foreach (array_keys($this->rsm->metaMappings, $joinColumn) as $alias) {
if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
$sqlIdentifier[$property] = $alias;
}
}
}
}
if (count($rootIdentifier) != count($sqlIdentifier)) {
throw new \RuntimeException(sprintf('Not all identifier properties can be found in the ResultSetMapping: %s', implode(', ', array_diff($rootIdentifier, array_keys($sqlIdentifier)))));
}
// Build the counter query
$sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql);
if ($this->platform instanceof PostgreSqlPlatform) {
//http://www.doctrine-project.org/jira/browse/DDC-1958
$this->getPostgresqlSql($AST, $sqlIdentifier, $innerSql, $sql);
}
// Apply the limit and offset
$sql = $this->platform->modifyLimitQuery($sql, $this->maxResults, $this->firstResult);
// Add the columns to the ResultSetMapping. It's not really nice but
// it works. Preferably I'd clear the RSM or simply create a new one
// but that is not possible from inside the output walker, so we dirty
// up the one we have.
foreach ($sqlIdentifier as $property => $alias) {
$this->rsm->addScalarResult($alias, $property);
}
return $sql;
}
/**
* Gets the SELECT SQL to select one or more entities by a set of field criteria.
*
* @param array $criteria
* @param AssociationMapping $assoc
* @param string $orderBy
* @param int $lockMode
* @param int $limit
* @param int $offset
* @param array $orderBy
* @return string
* @todo Refactor: _getSelectSQL(...)
*/
protected function _getSelectEntitiesSQL(array $criteria, $assoc = null, $lockMode = 0, $limit = null, $offset = null, array $orderBy = null)
{
$joinSql = $assoc != null && $assoc['type'] == ClassMetadata::MANY_TO_MANY ? $this->_getSelectManyToManyJoinSQL($assoc) : '';
$conditionSql = $this->_getSelectConditionSQL($criteria, $assoc);
$orderBy = $assoc !== null && isset($assoc['orderBy']) ? $assoc['orderBy'] : $orderBy;
$orderBySql = $orderBy ? $this->_getOrderBySQL($orderBy, $this->_getSQLTableAlias($this->_class->name)) : '';
$lockSql = '';
if ($lockMode == LockMode::PESSIMISTIC_READ) {
$lockSql = ' ' . $this->_platform->getReadLockSql();
} else {
if ($lockMode == LockMode::PESSIMISTIC_WRITE) {
$lockSql = ' ' . $this->_platform->getWriteLockSql();
}
}
return $this->_platform->modifyLimitQuery('SELECT ' . $this->_getSelectColumnListSQL() . $this->_platform->appendLockHint(' FROM ' . $this->_class->getQuotedTableName($this->_platform) . ' ' . $this->_getSQLTableAlias($this->_class->name), $lockMode) . $this->_selectJoinSql . $joinSql . ($conditionSql ? ' WHERE ' . $conditionSql : '') . $orderBySql, $limit, $offset) . $lockSql;
}
/**
* {@inheritdoc}
*/
public function walkSelectStatement(AST\SelectStatement $AST)
{
$sql = $this->walkSelectClause($AST->selectClause);
$sql .= $this->walkFromClause($AST->fromClause);
$sql .= $this->walkWhereClause($AST->whereClause);
$sql .= $AST->groupByClause ? $this->walkGroupByClause($AST->groupByClause) : '';
$sql .= $AST->havingClause ? $this->walkHavingClause($AST->havingClause) : '';
if (($orderByClause = $AST->orderByClause) !== null) {
$sql .= $AST->orderByClause ? $this->walkOrderByClause($AST->orderByClause) : '';
} else {
if (($orderBySql = $this->_generateOrderedCollectionOrderByItems()) !== '') {
$sql .= ' ORDER BY ' . $orderBySql;
}
}
$sql = $this->platform->modifyLimitQuery($sql, $this->query->getMaxResults(), $this->query->getFirstResult());
if (($lockMode = $this->query->getHint(Query::HINT_LOCK_MODE)) !== false) {
switch ($lockMode) {
case LockMode::PESSIMISTIC_READ:
$sql .= ' ' . $this->platform->getReadLockSQL();
break;
case LockMode::PESSIMISTIC_WRITE:
$sql .= ' ' . $this->platform->getWriteLockSQL();
break;
case LockMode::OPTIMISTIC:
foreach ($this->selectedClasses as $selectedClass) {
if (!$selectedClass['class']->isVersioned) {
throw \Doctrine\ORM\OptimisticLockException::lockFailed($selectedClass['class']->name);
}
}
break;
case LockMode::NONE:
break;
default:
throw \Doctrine\ORM\Query\QueryException::invalidLockMode();
}
}
return $sql;
}
/**
* Gets the SELECT SQL to select one or more entities by a set of field criteria.
*
* @param array|\Doctrine\Common\Collections\Criteria $criteria
* @param array|null $assoc
* @param int $lockMode
* @param int|null $limit
* @param int|null $offset
* @param array|null $orderBy
*
* @return string
*/
protected function getSelectSQL($criteria, $assoc = null, $lockMode = 0, $limit = null, $offset = null, array $orderBy = null)
{
$lockSql = '';
$joinSql = '';
$orderBySql = '';
if ($assoc != null && $assoc['type'] == ClassMetadata::MANY_TO_MANY) {
$joinSql = $this->getSelectManyToManyJoinSQL($assoc);
}
if (isset($assoc['orderBy'])) {
$orderBy = $assoc['orderBy'];
}
if ($orderBy) {
$orderBySql = $this->getOrderBySQL($orderBy, $this->getSQLTableAlias($this->class->name));
}
$conditionSql = ($criteria instanceof Criteria)
? $this->getSelectConditionCriteriaSQL($criteria)
: $this->getSelectConditionSQL($criteria, $assoc);
switch ($lockMode) {
case LockMode::PESSIMISTIC_READ:
$lockSql = ' ' . $this->platform->getReadLockSql();
break;
case LockMode::PESSIMISTIC_WRITE:
$lockSql = ' ' . $this->platform->getWriteLockSql();
break;
}
$columnList = $this->getSelectColumnsSQL();
$tableAlias = $this->getSQLTableAlias($this->class->name);
$filterSql = $this->generateFilterConditionSQL($this->class, $tableAlias);
$tableName = $this->quoteStrategy->getTableName($this->class, $this->platform);
if ('' !== $filterSql) {
$conditionSql = $conditionSql
? $conditionSql . ' AND ' . $filterSql
: $filterSql;
}
$select = 'SELECT ' . $columnList;
$from = ' FROM ' . $tableName . ' '. $tableAlias;
$join = $this->selectJoinSql . $joinSql;
$where = ($conditionSql ? ' WHERE ' . $conditionSql : '');
$lock = $this->platform->appendLockHint($from, $lockMode);
$query = $select
. $lock
. $join
. $where
. $orderBySql;
return $this->platform->modifyLimitQuery($query, $limit, $offset) . $lockSql;
}
/**
* {@inheritdoc}
*/
public function walkSelectStatement(AST\SelectStatement $AST)
{
$limit = $this->query->getMaxResults();
$offset = $this->query->getFirstResult();
$lockMode = $this->query->getHint(Query::HINT_LOCK_MODE);
$sql = $this->walkSelectClause($AST->selectClause) . $this->walkFromClause($AST->fromClause) . $this->walkWhereClause($AST->whereClause);
if ($AST->groupByClause) {
$sql .= $this->walkGroupByClause($AST->groupByClause);
}
if ($AST->havingClause) {
$sql .= $this->walkHavingClause($AST->havingClause);
}
if ($AST->orderByClause) {
$sql .= $this->walkOrderByClause($AST->orderByClause);
}
if (!$AST->orderByClause && ($orderBySql = $this->_generateOrderedCollectionOrderByItems())) {
$sql .= ' ORDER BY ' . $orderBySql;
}
if ($limit !== null || $offset !== null) {
$sql = $this->platform->modifyLimitQuery($sql, $limit, $offset);
}
if ($lockMode === null || $lockMode === false || $lockMode === LockMode::NONE) {
return $sql;
}
if ($lockMode === LockMode::PESSIMISTIC_READ) {
return $sql . ' ' . $this->platform->getReadLockSQL();
}
if ($lockMode === LockMode::PESSIMISTIC_WRITE) {
return $sql . ' ' . $this->platform->getWriteLockSQL();
}
if ($lockMode !== LockMode::OPTIMISTIC) {
throw QueryException::invalidLockMode();
}
foreach ($this->selectedClasses as $selectedClass) {
if (!$selectedClass['class']->isVersioned) {
throw OptimisticLockException::lockFailed($selectedClass['class']->name);
}
}
return $sql;
}
/**
* Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT.
*
* @param SelectStatement $AST
* @param bool $addMissingItemsFromOrderByToSelect
*
* @return string
*
* @throws \RuntimeException
*/
public function walkSelectStatement(SelectStatement $AST, $addMissingItemsFromOrderByToSelect = true)
{
// We don't want to call this recursively!
if ($AST->orderByClause instanceof OrderByClause && $addMissingItemsFromOrderByToSelect) {
// In the case of ordering a query by columns from joined tables, we
// must add those columns to the select clause of the query BEFORE
// the SQL is generated.
$this->addMissingItemsFromOrderByToSelect($AST);
}
// Remove order by clause from the inner query
// It will be re-appended in the outer select generated by this method
$orderByClause = $AST->orderByClause;
$AST->orderByClause = null;
// Set every select expression as visible(hidden = false) to
// make $AST have scalar mappings properly - this is relevant for referencing selected
// fields from outside the subquery, for example in the ORDER BY segment
$hiddens = array();
foreach ($AST->selectClause->selectExpressions as $idx => $expr) {
$hiddens[$idx] = $expr->hiddenAliasResultVariable;
$expr->hiddenAliasResultVariable = false;
}
$innerSql = parent::walkSelectStatement($AST);
// Restore orderByClause
$AST->orderByClause = $orderByClause;
// Restore hiddens
foreach ($AST->selectClause->selectExpressions as $idx => $expr) {
$expr->hiddenAliasResultVariable = $hiddens[$idx];
}
// Find out the SQL alias of the identifier column of the root entity.
// It may be possible to make this work with multiple root entities but that
// would probably require issuing multiple queries or doing a UNION SELECT.
// So for now, it's not supported.
// Get the root entity and alias from the AST fromClause.
$from = $AST->fromClause->identificationVariableDeclarations;
if (count($from) !== 1) {
throw new \RuntimeException("Cannot count query which selects two FROM components, cannot make distinction");
}
$fromRoot = reset($from);
$rootAlias = $fromRoot->rangeVariableDeclaration->aliasIdentificationVariable;
$rootClass = $this->queryComponents[$rootAlias]['metadata'];
$rootIdentifier = $rootClass->identifier;
// For every identifier, find out the SQL alias by combing through the ResultSetMapping
$sqlIdentifier = array();
foreach ($rootIdentifier as $property) {
if (isset($rootClass->fieldMappings[$property])) {
foreach (array_keys($this->rsm->fieldMappings, $property) as $alias) {
if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
$sqlIdentifier[$property] = $alias;
}
}
}
if (isset($rootClass->associationMappings[$property])) {
$joinColumn = $rootClass->associationMappings[$property]['joinColumns'][0]['name'];
foreach (array_keys($this->rsm->metaMappings, $joinColumn) as $alias) {
if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
$sqlIdentifier[$property] = $alias;
}
}
}
}
if (count($sqlIdentifier) === 0) {
throw new \RuntimeException('The Paginator does not support Queries which only yield ScalarResults.');
}
if (count($rootIdentifier) != count($sqlIdentifier)) {
throw new \RuntimeException(sprintf('Not all identifier properties can be found in the ResultSetMapping: %s', implode(', ', array_diff($rootIdentifier, array_keys($sqlIdentifier)))));
}
// Build the counter query
$sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql);
// http://www.doctrine-project.org/jira/browse/DDC-1958
$sql = $this->preserveSqlOrdering($sqlIdentifier, $innerSql, $sql, $orderByClause);
// Apply the limit and offset.
$sql = $this->platform->modifyLimitQuery($sql, $this->maxResults, $this->firstResult);
// Add the columns to the ResultSetMapping. It's not really nice but
// it works. Preferably I'd clear the RSM or simply create a new one
// but that is not possible from inside the output walker, so we dirty
// up the one we have.
foreach ($sqlIdentifier as $property => $alias) {
$this->rsm->addScalarResult($alias, $property);
}
return $sql;
}
