/**
* @param Query $query
* @param array $paramMappings
* @return array
* @throws \Doctrine\ORM\Query\QueryException
*/
protected function processParameterMappings(Query $query, $paramMappings)
{
$sqlParams = array();
$types = array();
/** @var Parameter $parameter */
foreach ($query->getParameters() as $parameter) {
$key = $parameter->getName();
if (!isset($paramMappings[$key])) {
throw QueryException::unknownParameter($key);
}
$value = $query->processParameterValue($parameter->getValue());
$type = $parameter->getValue() === $value ? $parameter->getType() : Query\ParameterTypeInferer::inferType($value);
foreach ($paramMappings[$key] as $position) {
$types[$position] = $type;
}
$sqlPositions = $paramMappings[$key];
$value = array($value);
$countValue = count($value);
for ($i = 0, $l = count($sqlPositions); $i < $l; $i++) {
$sqlParams[$sqlPositions[$i]] = $value[$i % $countValue];
}
}
if (count($sqlParams) != count($types)) {
throw QueryException::parameterTypeMissmatch();
}
if ($sqlParams) {
ksort($sqlParams);
$sqlParams = array_values($sqlParams);
ksort($types);
$types = array_values($types);
}
return array($sqlParams, $types);
}
/**
* Sets a parameter that can be used by the filter.
*
* @param string $name Name of the parameter.
* @param string $value Value of the parameter.
* @param string|null $type The parameter type. If specified, the given value will be run through
* the type conversion of this type. This is usually not needed for
* strings and numeric types.
*
* @return SQLFilter The current SQL filter.
*/
public final function setParameter($name, $value, $type = null)
{
if (null === $type) {
$type = ParameterTypeInferer::inferType($value);
}
$this->parameters[$name] = array('value' => $value, 'type' => $type);
// Keep the parameters sorted for the hash
ksort($this->parameters);
// The filter collection of the EM is now dirty
$this->em->getFilters()->setFiltersStateDirty();
return $this;
}
/**
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
* @SuppressWarnings(PHPMD.NPathComplexity)
*
* Copy of Doctrine\ORM\Query::processParameterMappings
*
* @param Query $query
* @return array
* @throws QueryException
*/
public function processParameterMappings(Query $query)
{
$parser = new Parser($query);
$parseResult = $parser->parse();
$paramMappings = $parseResult->getParameterMappings();
$resultSetMapping = $parseResult->getResultSetMapping();
$paramCount = count($query->getParameters());
$mappingCount = count($paramMappings);
if ($paramCount > $mappingCount) {
throw QueryException::tooManyParameters($mappingCount, $paramCount);
} elseif ($paramCount < $mappingCount) {
throw QueryException::tooFewParameters($mappingCount, $paramCount);
}
$sqlParams = [];
$types = [];
foreach ($query->getParameters() as $parameter) {
$key = $parameter->getName();
$value = $parameter->getValue();
$rsm = $resultSetMapping;
if (!isset($paramMappings[$key])) {
throw QueryException::unknownParameter($key);
}
if (isset($rsm->metadataParameterMapping[$key]) && $value instanceof ClassMetadata) {
$value = $value->getMetadataValue($rsm->metadataParameterMapping[$key]);
}
$value = $query->processParameterValue($value);
$type = $parameter->getValue() === $value ? $parameter->getType() : Query\ParameterTypeInferer::inferType($value);
foreach ($paramMappings[$key] as $position) {
$types[$position] = $type;
}
$sqlPositions = $paramMappings[$key];
// optimized multi value sql positions away for now,
// they are not allowed in DQL anyways.
$value = [$value];
$countValue = count($value);
for ($i = 0, $l = count($sqlPositions); $i < $l; $i++) {
$sqlParams[$sqlPositions[$i]] = $value[$i % $countValue];
}
}
if (count($sqlParams) !== count($types)) {
throw QueryException::parameterTypeMismatch();
}
if ($sqlParams) {
ksort($sqlParams);
$sqlParams = array_values($sqlParams);
ksort($types);
$types = array_values($types);
}
return [$sqlParams, $types];
}
/**
* Sets a query parameter.
*
* @param string|integer $key The parameter position or name.
* @param mixed $value The parameter value.
* @param string $type The parameter type. If specified, the given value will be run through
* the type conversion of this type. This is usually not needed for
* strings and numeric types.
* @return \Doctrine\ORM\AbstractQuery This query instance.
*/
public function setParameter($key, $value, $type = null)
{
$key = trim($key, ':');
$value = $this->processParameterValue($value);
if ($type === null) {
$type = Query\ParameterTypeInferer::inferType($value);
}
$this->_paramTypes[$key] = $type;
$this->_params[$key] = $value;
return $this;
}
/**
* Processes query parameter mappings.
*
* @param array $paramMappings
*
* @return array
*
* @throws Query\QueryException
*/
private function processParameterMappings($paramMappings)
{
$sqlParams = array();
$types = array();
foreach ($this->parameters as $parameter) {
$key = $parameter->getName();
$value = $parameter->getValue();
if (!isset($paramMappings[$key])) {
throw QueryException::unknownParameter($key);
}
if (isset($this->_resultSetMapping->metadataParameterMapping[$key]) && $value instanceof ClassMetadata) {
$value = $value->getMetadataValue($this->_resultSetMapping->metadataParameterMapping[$key]);
}
$value = $this->processParameterValue($value);
$type = $parameter->getValue() === $value ? $parameter->getType() : ParameterTypeInferer::inferType($value);
foreach ($paramMappings[$key] as $position) {
$types[$position] = $type;
}
$sqlPositions = $paramMappings[$key];
// optimized multi value sql positions away for now,
// they are not allowed in DQL anyways.
$value = array($value);
$countValue = count($value);
for ($i = 0, $l = count($sqlPositions); $i < $l; $i++) {
$sqlParams[$sqlPositions[$i]] = $value[$i % $countValue];
}
}
if (count($sqlParams) != count($types)) {
throw QueryException::parameterTypeMismatch();
}
if ($sqlParams) {
ksort($sqlParams);
$sqlParams = array_values($sqlParams);
ksort($types);
$types = array_values($types);
}
return array($sqlParams, $types);
}
/**
* {@inheritDoc}
*/
public function execute(Connection $conn, array $params, array $types)
{
// Create temporary id table
$conn->executeUpdate($this->_createTempTableSql);
try {
// Insert identifiers. Parameters from the update clause are cut off.
$numUpdated = $conn->executeUpdate($this->_insertSql, array_slice($params, $this->_numParametersInUpdateClause), array_slice($types, $this->_numParametersInUpdateClause));
// Execute UPDATE statements
foreach ($this->_sqlStatements as $key => $statement) {
$paramValues = [];
$paramTypes = [];
if (isset($this->_sqlParameters[$key])) {
foreach ($this->_sqlParameters[$key] as $parameterKey => $parameterName) {
$paramValues[] = $params[$parameterKey];
$paramTypes[] = isset($types[$parameterKey]) ? $types[$parameterKey] : ParameterTypeInferer::inferType($params[$parameterKey]);
}
}
$conn->executeUpdate($statement, $paramValues, $paramTypes);
}
} catch (\Exception $exception) {
// FAILURE! Drop temporary table to avoid possible collisions
$conn->executeUpdate($this->_dropTempTableSql);
// Re-throw exception
throw $exception;
}
// Drop temporary table
$conn->executeUpdate($this->_dropTempTableSql);
return $numUpdated;
}
/**
* Sets a query parameter.
*
* @param string|integer $key The parameter position or name.
* @param mixed $value The parameter value.
* @param string $type The parameter type. If specified, the given value will be run through
* the type conversion of this type. This is usually not needed for
* strings and numeric types.
* @return Doctrine\ORM\AbstractQuery This query instance.
*/
public function setParameter($key, $value, $type = null)
{
if ($type === null) {
$type = Query\ParameterTypeInferer::inferType($value);
}
$this->_paramTypes[$key] = $type;
$this->_params[$key] = $value;
return $this;
}
public function testSetParameter()
{
$qb = $this->_em->createQueryBuilder()->select('u')->from('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u')->where('u.id = :id')->setParameter('id', 1);
$parameter = new Parameter('id', 1, ParameterTypeInferer::inferType(1));
$this->assertEquals($parameter, $qb->getParameter('id'));
}
/**
* Sets a query parameter for the query being constructed.
*
* <code>
* $qb = $em->createQueryBuilder()
* ->select('u')
* ->from('User', 'u')
* ->where('u.id = :user_id')
* ->setParameter('user_id', 1);
* </code>
*
* @param string|integer $key The parameter position or name.
* @param mixed $value The parameter value.
* @param string|null $type PDO::PARAM_* or \Doctrine\DBAL\Types\Type::* constant
* @return QueryBuilder This QueryBuilder instance.
*/
public function setParameter($key, $value, $type = null)
{
$key = trim($key, ':');
$this->_paramTypes[$key] = $type ?: Query\ParameterTypeInferer::inferType($value);
$this->_params[$key] = $value;
return $this;
}
/**
* Initializes a new <tt>MultiTableUpdateExecutor</tt>.
*
* @param Node $AST The root AST node of the DQL query.
* @param SqlWalker $sqlWalker The walker used for SQL generation from the AST.
* @internal Any SQL construction and preparation takes place in the constructor for
* best performance. With a query cache the executor will be cached.
*/
public function __construct(AST\Node $AST, $sqlWalker)
{
$em = $sqlWalker->getEntityManager();
$conn = $em->getConnection();
$platform = $conn->getDatabasePlatform();
$quoteStrategy = $em->getConfiguration()->getQuoteStrategy();
$updateClause = $AST->updateClause;
$primaryClass = $sqlWalker->getEntityManager()->getClassMetadata($updateClause->abstractSchemaName);
$rootClass = $em->getClassMetadata($primaryClass->rootEntityName);
$updateItems = $updateClause->updateItems;
$tempTable = $platform->getTemporaryTableName($rootClass->getTemporaryIdTableName());
$idColumnNames = $rootClass->getIdentifierColumnNames();
$idColumnList = implode(', ', $idColumnNames);
// 1. Create an INSERT INTO temptable ... SELECT identifiers WHERE $AST->getWhereClause()
$sqlWalker->setSQLTableAlias($primaryClass->getTableName(), 't0', $updateClause->aliasIdentificationVariable);
$this->_insertSql = 'INSERT INTO ' . $tempTable . ' (' . $idColumnList . ')' . ' SELECT t0.' . implode(', t0.', $idColumnNames);
$rangeDecl = new AST\RangeVariableDeclaration($primaryClass->name, $updateClause->aliasIdentificationVariable);
$fromClause = new AST\FromClause(array(new AST\IdentificationVariableDeclaration($rangeDecl, null, array())));
$this->_insertSql .= $sqlWalker->walkFromClause($fromClause);
// 2. Create ID subselect statement used in UPDATE ... WHERE ... IN (subselect)
$idSubselect = 'SELECT ' . $idColumnList . ' FROM ' . $tempTable;
// 3. Create and store UPDATE statements
$classNames = array_merge($primaryClass->parentClasses, array($primaryClass->name), $primaryClass->subClasses);
$i = -1;
foreach (array_reverse($classNames) as $className) {
$affected = false;
$class = $em->getClassMetadata($className);
$updateSql = 'UPDATE ' . $quoteStrategy->getTableName($class, $platform) . ' SET ';
foreach ($updateItems as $updateItem) {
$field = $updateItem->pathExpression->field;
if (isset($class->fieldMappings[$field]) && !isset($class->fieldMappings[$field]['inherited']) || isset($class->associationMappings[$field]) && !isset($class->associationMappings[$field]['inherited'])) {
$newValue = $updateItem->newValue;
if (!$affected) {
$affected = true;
++$i;
} else {
$updateSql .= ', ';
}
$updateSql .= $sqlWalker->walkUpdateItem($updateItem);
//FIXME: parameters can be more deeply nested. traverse the tree.
//FIXME (URGENT): With query cache the parameter is out of date. Move to execute() stage.
if ($newValue instanceof AST\InputParameter) {
$parameterName = $newValue->name;
$parameter = $sqlWalker->getQuery()->getParameter($parameterName);
$value = $sqlWalker->getQuery()->processParameterValue($parameter->getValue());
$type = $parameter->getValue() === $value ? $parameter->getType() : ParameterTypeInferer::inferType($value);
$this->_sqlParameters[$i]['parameters'][] = $value;
$this->_sqlParameters[$i]['types'][] = $type;
++$this->_numParametersInUpdateClause;
}
}
}
if ($affected) {
$this->_sqlStatements[$i] = $updateSql . ' WHERE (' . $idColumnList . ') IN (' . $idSubselect . ')';
}
}
// Append WHERE clause to insertSql, if there is one.
if ($AST->whereClause) {
$this->_insertSql .= $sqlWalker->walkWhereClause($AST->whereClause);
}
// 4. Store DDL for temporary identifier table.
$columnDefinitions = array();
foreach ($idColumnNames as $idColumnName) {
$columnDefinitions[$idColumnName] = array('notnull' => true, 'type' => Type::getType($rootClass->getTypeOfColumn($idColumnName)));
}
$this->_createTempTableSql = $platform->getCreateTemporaryTableSnippetSQL() . ' ' . $tempTable . ' (' . $platform->getColumnDeclarationListSQL($columnDefinitions) . ')';
$this->_dropTempTableSql = $platform->getDropTemporaryTableSQL($tempTable);
}
/**
* @dataProvider providerParameterTypeInferer
*/
public function testParameterTypeInferer($value, $expected)
{
$this->assertEquals($expected, ParameterTypeInferer::inferType($value));
}
/**
* @param Statement $sth
* @param ArrayCollection $parameters
*
* @return Statement
*
* @throws \Doctrine\DBAL\DBALException
*/
private function bindParameters(Statement $sth, array $parameters)
{
foreach ($parameters as $key => $value) {
$typeInferer = Query\ParameterTypeInferer::inferType($value);
if (!is_int($typeInferer)) {
$findType = Type::getType($typeInferer);
$value = $findType->convertToDatabaseValue($value, new MySqlPlatform());
$bindingType = $findType->getBindingType();
} else {
$bindingType = $typeInferer;
}
$sth->bindValue($key, $value, $bindingType);
}
return $sth;
}
