[RFC] Set multiple parameters at once in the QueryBuilder #6579

New Issue

admin · 2026-01-22T15:35:15+01:00

admin commented

2026-01-22 15:35:15 +01:00

Originally created by @Mediagone on GitHub (Dec 3, 2020).

Hi everybody,

I would like to resurrect the discussion about the ability to add multiple parameters to the querybuilder at once.

Similar subjects was previously mentioned in the following discussions, without leading to a concrete solution:

First attempt

I'm looking for a way to handle parameter lists (eg. IN clauses). Since setParameters() allows array as parameter value, I first tried to do:

$entityIds = [ ... ];

$qb->andWhere('entity.id IN (:list)')
   ->setParameter('list', $entityIds);

However, we cannot specify a custom doctrine type for items in an array, it only works with INT or STRING values.

In my case, IDs are objects stored as binary in the DB, but they implements __toString so they are automatically converted to strings internally and the comparison fails.

SQL output (ids are converted to base 62 strings):

SELECT e0_.id AS id_0 FROM entity e0_ WHERE e0_.id IN ('3Rfh8qv5YUz', '4Mkaztpck9h');

Expected SQL (binary values):

SELECT e0_.id AS id_0 FROM entity e0_ WHERE e0_.id IN (0x175F78BAF6C34282, 0x175F78BAF6C5DD23);

Second attempt

We can specify a custom type if we bind parameters separately, so we could do:

$qb->andWhere('entity.id IN (:id' . implode(',:id', array_keys($entityIds)) . ')');

foreach ($entityIds as $key => $id) {
    $qb->setParameter("id$key", $id, 'custom_id');
}

However it kinda defeats the main advantage of query builder. We can write a little helper class to ease parameters binding, but this would still not be satisfactory enough, as we cannot use the fluent interface.

$idList = new QueryParameterList(
    $entityIds,
    'id',           // prefix for parameter names (id0, id1, id2...)
    'custom_id'     // Doctrine type for elements in $entityIds
);

$qb->andWhere("entity.id IN $idList");
$idList->bindParameters($qb);

Third attempt

The querybuilder also exposes the setParameters() method, which accepts multiple parameters:

$idList = new QueryParameterList($entityIds, 'id', 'custom_id');

$qb
   ->...
   ->andWhere("entity.id IN $idList")
   ->addParameters($idList->getParameters()); // getParameters returns an ArrayCollection

Bad news: setParameters discards all previously defined parameters! It's a serious problem when using criteria that can be combined in any order.

Considered solutions

Add a new addParameters() method that accepts the same arguments as setParameters, but acts like setParameter in a loop.
Modify setParameter() (and subsequent classes) to handle an array of custom doctrine types (not only int/string arrays).
Refactor parameter handling and create a ParameterCollection with "handy methods" to manage the collection (cf. guilhermeblanco comments in previous discussions). However, it sounds like a huge task and BC break.

What do you think would the best solution? I can implement it as soon as I have the green light from maintainers.

Thanks for reading :)

Originally created by @Mediagone on GitHub (Dec 3, 2020). Hi everybody, I would like to resurrect the discussion about the ability to add multiple parameters to the querybuilder at once. Similar subjects was previously mentioned in the following discussions, without leading to a concrete solution: - https://github.com/doctrine/orm/pull/512 - https://github.com/doctrine/orm/pull/648 ## First attempt I'm looking for a way to handle parameter lists (eg. IN clauses). Since `setParameters()` allows array as parameter value, I first tried to do: ```php $entityIds = [ ... ]; $qb->andWhere('entity.id IN (:list)') ->setParameter('list', $entityIds); ``` However, we cannot specify a custom doctrine type for items in an array, it only works with INT or STRING values. In my case, IDs are objects stored as binary in the DB, but they implements `__toString` so they are automatically converted to strings internally and the comparison fails. SQL output (ids are converted to base 62 strings): ```sql SELECT e0_.id AS id_0 FROM entity e0_ WHERE e0_.id IN ('3Rfh8qv5YUz', '4Mkaztpck9h'); ``` Expected SQL (binary values): ```sql SELECT e0_.id AS id_0 FROM entity e0_ WHERE e0_.id IN (0x175F78BAF6C34282, 0x175F78BAF6C5DD23); ``` ## Second attempt We can specify a custom type if we bind parameters separately, so we could do: ```php $qb->andWhere('entity.id IN (:id' . implode(',:id', array_keys($entityIds)) . ')'); foreach ($entityIds as $key => $id) { $qb->setParameter("id$key", $id, 'custom_id'); } ``` However it kinda defeats the main advantage of query builder. We can write a little helper class to ease parameters binding, but this would still not be satisfactory enough, as we cannot use the fluent interface. ```php $idList = new QueryParameterList( $entityIds, 'id', // prefix for parameter names (id0, id1, id2...) 'custom_id' // Doctrine type for elements in $entityIds ); $qb->andWhere("entity.id IN $idList"); $idList->bindParameters($qb); ``` ## Third attempt The querybuilder also exposes the `setParameters()` method, which accepts multiple parameters: ```php $idList = new QueryParameterList($entityIds, 'id', 'custom_id'); $qb ->... ->andWhere("entity.id IN $idList") ->addParameters($idList->getParameters()); // getParameters returns an ArrayCollection ``` Bad news: `setParameters` discards all previously defined parameters! It's a serious problem when using criteria that can be combined in any order. ## Considered solutions 1) Add a new `addParameters()` method that accepts the same arguments as `setParameters`, but acts like `setParameter` in a loop. 2) Modify `setParameter()` (and subsequent classes) to handle an array of custom doctrine types (not only int/string arrays). 3) Refactor parameter handling and create a `ParameterCollection` with "handy methods" to manage the collection (cf. guilhermeblanco comments in previous discussions). However, it sounds like a huge task and BC break. What do you think would the best solution? I can implement it as soon as I have the green light from maintainers. Thanks for reading :)

admin commented

2026-01-22 15:35:15 +01:00

@greg0ire commented on GitHub (Dec 3, 2020):

Hi! I think the very best solution would be something that allows you to write the following:

$entities = [ ... ];

$qb->andWhere('entity IN (:list)')
   ->setParameter('list', $entities);

I believe this already works since https://github.com/doctrine/orm/pull/590, but that wasn't documented, sadly.
The code that allows this to work is here: 01187c9260/lib/Doctrine/ORM/UnitOfWork.php (L3033-L3035)

When we discussed this on Slack, you said it had the same behavior as the first version you posted, with .id. I'd recommend you try debugging the piece of code above and find where the unexpected conversion happens, maybe this is just one small bugfix away from working.

@greg0ire commented on GitHub (Dec 3, 2020): Hi! I think the very best solution would be something that allows you to write the following: ```php $entities = [ ... ]; $qb->andWhere('entity IN (:list)') ->setParameter('list', $entities); ``` I believe this already works since https://github.com/doctrine/orm/pull/590, but that wasn't documented, sadly. The code that allows this to work is here: https://github.com/doctrine/orm/blob/01187c9260cd085529ddd1273665217cae659640/lib/Doctrine/ORM/UnitOfWork.php#L3033-L3035 When we discussed this on Slack, you said it had the same behavior as the first version you posted, with `.id`. I'd recommend you try debugging the piece of code above and find where the unexpected conversion happens, maybe this is just one small bugfix away from working.

admin commented

2026-01-22 15:35:17 +01:00

@Mediagone commented on GitHub (Dec 5, 2020):

Interesting, I never used entities directly in DQL for IN clauses, I'll keep that in mind for the future.

However, it doesn't solve the whole problem because I don't always manipulate entities, but only IDs as plain Value Objects. So I still need a way to define the Doctrine type manually.

@Mediagone commented on GitHub (Dec 5, 2020): Interesting, I never used entities directly in DQL for IN clauses, I'll keep that in mind for the future. However, it doesn't solve the whole problem because I don't always manipulate entities, but only IDs as plain Value Objects. So I still need a way to define the Doctrine type manually.

admin commented

2026-01-22 15:35:17 +01:00

@driskell commented on GitHub (Mar 27, 2023):

Would be great to see some support on this. As DX goes this threw me a good few hours and had to learn some internals far more than I intended. It seems to make custom types a fairly "poor-man" choice as they're not fully supported in all places.

@driskell commented on GitHub (Mar 27, 2023): Would be great to see some support on this. As DX goes this threw me a good few hours and had to learn some internals far more than I intended. It seems to make custom types a fairly "poor-man" choice as they're not fully supported in all places.

admin referenced this issue

2026-01-22 16:06:16 +01:00

[PR #6579] Document `string` as possible parameter type for `Expr#between()` #10050

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6579