How to match parameters from getParameters with getSQL?

[LorisZ]

When I build a query from QueryBuilder with DQL and want to use the resulting Query somewhere else, I try to do

$qb = ...;
$query = $qb->getQuery();
$sql = $query->getSQL();
$parameters = $query->getParameters();

Which gives me something like this:

$sql:

SELECT t0_.id AS id_0
FROM test t0_
WHERE t0_.a = ? AND t0_.b > ? AND t0_.b < ?

$parameters:

[
  "a" => 1,
  "b" => 5,
]

How am I supposed to match the ? in the query with the named parameters? I thought maybe it's in the same order, but since even the numbers don't necessarily match (named parameters can be used several times as in the example above), it's just not possible. I have not found another public method or different parameters that would help me here. Also found some similar posts on stackoverflow, but short from guessing or modifying the doctrine files I did not really find any satisfying answers...

I was trying this on version 2.5.14, but when I took a quick look in the source code it did not seem any different even on 3.0.

[bakugo]

I was trying to do something similar and figured it out: you have to manually parse the query with a Doctrine\ORM\Query\Parser, the resulting ParserResult object tells you the SQL parameter positions for each named DQL parameter. Here's an example of executing a DQL query as a plain SQL query:

$parser = new Parser($query);

$parserResult = $parser->parse();

$params = [];
$types = [];

foreach ($query->getParameters() as $parameter) {
	$positions = $parserResult->getSqlParameterPositions($parameter->getName());
	
	foreach ($positions as $position) {
		$params[$position] = $parameter->getValue();
		$types[$position] = $parameter->getType();
	}
}

$result = $query->getEntityManager()->getConnection()->executeQuery($query->getSQL(), $params, $types);

$rows = $result->fetchAllAssociative();

[LorisZ]

Interesting, I hope I find some time to try it out this way. For now I only fetched all the ids and used them as param for the other query instead of using a subquery.

It's still a bit clonky for something that could/should work out of the box IMO, but it's something, thanks.