QueryBuilder Does Not Convert UUIDs to Binary Format for SQLite Queries

[gnito-org]

Bug Report

Q	A
BC Break	no
Version	3.1.0

Summary

When using UUIDs as keys in a SQLite database, the ServiceEntityRepositoryProxy::find(), findBy() andfindOneBy() methods convert a UUID to binary format when quering the database.

When using ServiceEntityRepositoryProxy::createQueryBuilder(), the UUID is not converted to binary format and hence the DBAL query finds no records in the SQLite database.

Current behavior

$companyId = Uuid::fromRfc4122('018e474e-011c-7688-9d12-41b6471c82a8');

$company = $companyRepository->find($companyId);
// $company is an instance of Company

$company = $companyRepository->createQueryBuilder('c')
    ->andWhere('c.id = :id')
    ->setParameter('id', $companyId)
    ->getQuery()
    ->execute();
// $company is NULL

The same problem occurs with foreign keys. They work as expected in find methods and do not work as expected in QueryBuilder.

This issue is particularly problematic with foreign keys. With ->andWhere('c.id = :id') you can manually convert the id to binary in your app if the database platform is SQLite, but with ->andWhere('c.user = :user') you cannot.

Symfony Profiler for Find

SELECT t0.id AS id_1, t0.company_name AS company_name_2, t0.created_at AS created_at_3, t0.deleted_at AS deleted_at_4, t0.user_id AS user_id_5 FROM company t0 WHERE t0.id = ?
Parameters:
[▼
  b"\x01ÄGN\x01\x1CvêØ\x12AÂG\x1Cé¿"
]
View formatted query
#   File/Call
1   Doctrine\DBAL\Connection->executeQuery 
2   Doctrine\ORM\Persisters\Entity\BasicEntityPersister->load 
3   Doctrine\ORM\Persisters\Entity\BasicEntityPersister->loadById 
4   Doctrine\ORM\EntityManager->find 
5   Doctrine\ORM\EntityRepository->find 
6   Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepositoryProxy->find 
7   App\Controller\TestController->index 
8   Symfony\Component\HttpKernel\HttpKernel->handleRaw 
9   Symfony\Component\HttpKernel\HttpKernel->handle 
10  Symfony\Component\HttpKernel\Kernel->handle 
11  Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run 
12  require_once 

Symfony Profiler for QueryBuilder

SELECT c0_.id AS id_0, c0_.company_name AS company_name_1, c0_.created_at AS created_at_2, c0_.deleted_at AS deleted_at_3, c0_.user_id AS user_id_4 FROM company c0_ WHERE c0_.id = ?
Parameters:
[▼
Symfony\Component\Uid\UuidV7 {#1680 ▶}
]
View formatted query
#   File/Call
1   Doctrine\DBAL\Connection->executeQuery 
2   Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute 
3   Doctrine\ORM\Query->_doExecute 
4   Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache 
5   Doctrine\ORM\AbstractQuery->execute 
6   App\Controller\TestController->index 
7   Symfony\Component\HttpKernel\HttpKernel->handleRaw 
8   Symfony\Component\HttpKernel\HttpKernel->handle 
9   Symfony\Component\HttpKernel\Kernel->handle 
10  Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run 
11  require_once 

Expected behavior

QueryBuilder should also convert the UUID to binary format for SQLite queries.

[derrabus]

You did not specify the type of the parameter in your setParameter() call.

[gnito-org]

I do not want to hardcode SQLite in my app. It must work seamlessly with a PostgreSQL, MySQL or SQLite database.

Besides, how do I specify the type of parameter for below in a way that transforms the ids to binary?:

$company = $companyRepository->createQueryBuilder('c')
    ->andWhere('c.id IN (:ids)')
    ->setParameter('ids', $companyIds)
    ->getQuery()
    ->execute();

[gnito-org]

Furthermore, this query:

$companyRepository->createQueryBuilder('c')->andWhere('c.user = :user')->setParameter('user', $user, Types::BINARY)->getQuery()

crashes with:

Object of class App\Users\Entity\User could not be converted to string

    public function __toString(): string
    {
        return $this->id->toRfc4122();
    }

does not find any records.

    public function __toString(): string
    {
        return $this->id->toBinary();
    }

finds records on SQLite but does not find records on other database platforms.