uuid_binary_ordered_time not working with IN expression

[ger86]

Hi,

I have an entity with its identifier sets as uuid_binary_ordered_time. When I try to use a IN expression with queryBuilder, I cannot get it work.

I try with different ways but none of them works well: I get no results.

First attempt:

$queryBuilder
    ->andWhere('t.id IN (:ids)')
    ->setParameter(
        'ids',
        array_map(fn (string $id) => Uuid::fromString($id)->getBytes(), $testIds)
    );

Second attempt:

// Second
$queryBuilder
    ->andWhere('t.id IN (:ids)')
    ->setParameter(
        'ids',
        implode(',', array_map(fn ($id) => UuidV1::fromString($id), $excludeIds)),
        UuidBinaryOrderedTimeType::NAME
    );

I can make it work with the ugly following code, but for performance reasons (and for avoiding tricky code 😂 ) I prefer to make it work using IN clause:

$clauseTemplate = 't.id = :testId%d';
$clauses = [];
foreach ($testIds as $key => $testId) {
    $clauses[] = sprintf($clauseTemplate, $key);
    $queryBuilder->setParameter(
        sprintf('testId%d', $key),
        $testId,
        UuidBinaryOrderedTimeType::NAME
    );
}
$queryBuilder->andWhere(implode(' OR ', $clauses));

Is this a known bug?

Before posting this issue I have searched the issued and found a possible answer here #18 (comment) but it doesn't work.

[ramsey]

I'm not familiar enough with how the query builder works with the IN expression. Are you able to do similar queries using IN with other data types? That is, does Doctrine do what you're expecting and expand the array into the :ids parameter?

I know you can't use bound parameters in the IN expression with PDO, etc., but maybe Doctrine supports it?

[jaapio]

        $codec = new OrderedTimeCodec(
            (new UuidFactory())->getUuidBuilder()
        );

        $queryBuilder = $this->createQueryBuilder('e');
        $queryBuilder->where(
            $queryBuilder->expr()->in(
                'e.id',
                ':ids'
            )
        );

        $queryBuilder->setParameter('ids', array_map(function(UuidInterface $uuid) use ($codec) {
            return $codec->encodeBinary($uuid);
        }, $ids));

This seems to work... maybe we should wrap this into a customized type allowing this transformation on array's?

[ToshY]

I'm encountering this issue now as well in Symfony 5.x, using the latest versions of uuid-doctrine 1.8.0 (doctrine/dbal 3.2.0, doctrine/orm 2.10.2).

Could not convert database value "��K��X(�$�B�� " to Doctrine Type uuid_binary_ordered_time

This happens for me when using the doctrine's paginator, which performs a WHERE IN query when $fetchJoinCollection is set to true.

[Gasjki]

@ToshY I just installed Ramsey UUID because I thought there's something wrong with the Symfony UID component.

I'll start investigating what's going on and I'll try to come up with a PR for the doctrine paginator.

[ramsey]

Was anyone able to figure out a solution for this?

[jaapio]

I didn't have the final solution, but my snippet works to solve the issue locally. We just need to find a way to put it into the normal flow of doctrine.

I am not at the project any more that had this issue, so it will be a bit harder for me to test this. But if you need some help, I will try to remember what the issue was.

[zedar187]

I'm currently running into this issue mentioned above. Versions used doctrine/dbal:3.8.3, doctrine/orm:2.13.5, ramsey/uuid:4.2.3, ramsey/uuid-doctrine:1.8.2, php:7.4 as well as 8.1.

A joined table having an id (AI int), 2x uuid_binary_ordered_time and another int. I can't seem to find a way using a where with IN. SELECTing one id is no problem, but as soon as an array/collection of multiple entities are set as parameter, the result is just empty.

Example code which works:

$qb = $em->createQueryBuilder();
$qry = $qb->select('me')
    ->from('App\Entity\MyEntity', 'me')
    ->where("me.field1 = :my_id");
    $qb->setParameter("my_id", $my_id, 'uuid_binary_ordered_time');

Example code which doesn't work (as in returns nothing):

$qb = $em->createQueryBuilder();
$qry = $qb->select('me')
    ->from('App\Entity\MyEntity', 'me')
    ->where("me.field1 IN (:multiple_ids)");
    $qb->setParameter("multiple_ids", $my_ids);
// $my_ids being a Doctrine collection; but tested with a "naked" array of ids, too

The query as is works fine when running via phpmyadmin for example, so the passed values are correct. Anyone having a hint for me? @jaapio 's code didn't work for me neither.

[tobiasgv]

The following worked for me:

use Doctrine\DBAL\ArrayParameterType;
use Ramsey\Uuid\Uuid;

$queryBuilder
    ->select('me')
    ->from('App\Entity\MyEntity', 'me')
    ->where($queryBuilder->expr()->in('me.identifier', ':identifiers'))
    ->setParameter(':identifiers', array_map(function (string $stringUuid): string {
        return Uuid::fromString($stringUuid)->getBytes();
    }, $stringUuids), ArrayParameterType::BINARY);

(ramsey/uuid-doctrine: 2.0.0, doctrine/orm: 2.19.5)

[des1roer]

use Ramsey\Uuid\UuidFactory;
use Ramsey\Uuid\UuidInterface;

 /**
     * @return string[]
     * @see \Ramsey\Uuid\Doctrine\UuidBinaryOrderedTimeType::encode
     */
    public static function fromStringsForDb(string ...$uuids): array
    {
        return array_map(static fn(string $value): string => self::timeCodec()->encodeBinary(
            self::factory()->fromString($value),
        ), $uuids);
    }

    private static function factory(): UuidFactory
    {
        static $factory;

        if (null === $factory) {
            $factory = new UuidFactory();
        }

        return $factory;
    }

    private static function timeCodec(): OrderedTimeCodec
    {
        static $timeCodec;

        if (null === $timeCodec) {
            $timeCodec = new OrderedTimeCodec(self::factory()->getUuidBuilder());
        }

        return $timeCodec;
    }

        $qb = $this->createQueryBuilder('fsq');
        $qb
            ->andWhere('fsq.uuid in (:uuids)')
            ->setParameter('uuids', self::fromStringsForDb(...$uuids), Connection::PARAM_STR_ARRAY);