[DoctrineBridge] Ulid is not converted to correct database type in queries #39135

warslett · 2020-11-21T11:00:07Z

Symfony version(s) affected: 5.2.0-RC2 (PHP 7.4.11 MySQL 8.0.22)

Description

I think this was an issue on RC1 as well but I only discovered it last night. When I run doctrine queries the Ulid field of parameters in the query are not being converted into the format of the corresponding column in the database meaning you don't get any matches.

The tldr; is that it is just passing the ulid in as a string when running queries and not converting it to the format in which it is stored in the database binary.

I have the following entities:

<?php

declare(strict_types=1);

namespace App\Domain\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Bridge\Doctrine\IdGenerator\UlidGenerator;
use Symfony\Component\Uid\Ulid;

/**
 * @ORM\Entity
 * @ORM\Table(uniqueConstraints={
 *      @ORM\UniqueConstraint(name="unique_organisation_member", columns={"user_ulid", "organisation_ulid"})
 * })
 */
class OrganisationMember
{

    /**
     * @ORM\Id
     * @ORM\Column(type="ulid", unique=true)
     * @ORM\GeneratedValue(strategy="CUSTOM")
     * @ORM\CustomIdGenerator(class=UlidGenerator::class)
     * @var Ulid|null
     */
    public ?Ulid $ulid = null;

    /**
     * @ORM\ManyToOne(targetEntity="User", fetch="EAGER")
     * @ORM\JoinColumn(name="user_ulid", referencedColumnName="ulid")
     */
    public User $user;

    /**
     * @ORM\ManyToOne(targetEntity="Organisation", fetch="EAGER")
     * @ORM\JoinColumn(name="organisation_ulid", referencedColumnName="ulid")
     */
    public Organisation $organisation;
}

and

<?php

declare(strict_types=1);

namespace App\Domain\Entity;

use Doctrine\ORM\Mapping as ORM;
use Serializable;
use Symfony\Component\Uid\Ulid;
use Symfony\Bridge\Doctrine\IdGenerator\UlidGenerator;
use Symfony\Component\Security\Core\User\UserInterface;

/**
 * @ORM\Entity
 */
class User implements UserInterface, Serializable
{

    /**
     * @ORM\Id
     * @ORM\Column(type="ulid", unique=true)
     * @ORM\GeneratedValue(strategy="CUSTOM")
     * @ORM\CustomIdGenerator(class=UlidGenerator::class)
     * @var Ulid|null
     */
    public ?Ulid $ulid = null;

   ...
}

And this migration:

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20201121100147 extends AbstractMigration
{
    public function getDescription() : string
    {
        return '';
    }

    public function up(Schema $schema) : void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE organisation (ulid BINARY(16) NOT NULL COMMENT \'(DC2Type:ulid)\', name VARCHAR(140) NOT NULL, PRIMARY KEY(ulid)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE organisation_member (ulid BINARY(16) NOT NULL COMMENT \'(DC2Type:ulid)\', user_ulid BINARY(16) DEFAULT NULL COMMENT \'(DC2Type:ulid)\', organisation_ulid BINARY(16) DEFAULT NULL COMMENT \'(DC2Type:ulid)\', INDEX IDX_F2FF2065B8098490 (user_ulid), INDEX IDX_F2FF206580CCC841 (organisation_ulid), UNIQUE INDEX unique_organisation_member (user_ulid, organisation_ulid), PRIMARY KEY(ulid)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user (ulid BINARY(16) NOT NULL COMMENT \'(DC2Type:ulid)\', email VARCHAR(254) NOT NULL, password VARCHAR(64) NOT NULL, is_active TINYINT(1) NOT NULL, UNIQUE INDEX UNIQ_8D93D649E7927C74 (email), PRIMARY KEY(ulid)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('ALTER TABLE organisation_member ADD CONSTRAINT FK_F2FF2065B8098490 FOREIGN KEY (user_ulid) REFERENCES user (ulid)');
        $this->addSql('ALTER TABLE organisation_member ADD CONSTRAINT FK_F2FF206580CCC841 FOREIGN KEY (organisation_ulid) REFERENCES organisation (ulid)');
    }

    public function down(Schema $schema) : void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('ALTER TABLE organisation_member DROP FOREIGN KEY FK_F2FF206580CCC841');
        $this->addSql('ALTER TABLE organisation_member DROP FOREIGN KEY FK_F2FF2065B8098490');
        $this->addSql('DROP TABLE organisation');
        $this->addSql('DROP TABLE organisation_member');
        $this->addSql('DROP TABLE user');
    }
}

In organisation_member I have this data:

ulid,user_ulid,organisation_ulid
0x0175EA4467C31B71469CABA912AB4D93,0x0175EA4402E2A586339ABAE13FB288B9,0x0175EA4467B0B0371DA5727C278649AD

In user I have this data:

ulid,...
0x0175EA4402E2A586339ABAE13FB288B9,...

When I run this query

/** @var User $user */
$user = $this->security->getUser();

$results = $this->entityManager->createQueryBuilder()
                ->select('om')
                ->from(OrganisationMember::class, 'om')
                ->where('om.user = :user')
                ->setParameter('user', $user) 
                ->setMaxResults(1)
                ->getQuery()
                ->getResult();

I get back 0 results even though the data is there. When I take a look in MySQL general_log I can see the following query was executed:

SELECT o0_.ulid AS ulid_0, o0_.user_ulid AS user_ulid_1, o0_.organisation_ulid AS organisation_ulid_2 FROM organisation_member o0_ WHERE o0_.user_ulid = '01EQN480Q2MP3376NTW4ZV525S' LIMIT 1

It is just passing the ulid in as a string and not converting it to the format in which it is stored in the database (binary)

If I manually run the query but substitute the parameter that doctrine used for the correctly encoded parameter I get one result back as expected:

SELECT o0_.ulid AS ulid_0, o0_.user_ulid AS user_ulid_1, o0_.organisation_ulid AS organisation_ulid_2 FROM organisation_member o0_ WHERE o0_.user_ulid = uuid_to_bin('0175ea44-02e2-a586-339a-bae13fb288b9') LIMIT 1

How to reproduce
Detailed steps above but the short version:

Create Entity with with ulid id field
Create Another Entity and associate it with previous entity
Insert a record into the table for the second entity correctly associated with a record in the table for the first entity
Switch on query logging in mysql by running SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Run a DQL query filtering results of entity 1 by association with an instance of entity 2 (see above example)
Note that 0 results are returned
Take a look at mysql.general_log table and locate your query
Note that it is searching by the ulid string which is not how the property is encoded in the database and hence does not return any results

Possible Solution
We need to bind uuid types by converting the value to binary and then using the binary parameter type.

Additional context
#39113 was merged for RC2 to change the way ulid is stored in the mysql from a uuid string to uuid binary however I also observed this same issue in RC1 where the data was stored in the db as uuid string but the query parameter was encoded as ulid.

The text was updated successfully, but these errors were encountered:

warslett · 2020-11-21T11:46:55Z

AbstractUidType does not have this method override which I think it needs:

    /**
     * Gets the (preferred) binding type for values of this type that
     * can be used when binding parameters to prepared statements.
     *
     * This method should return one of the {@link ParameterType} constants.
     *
     * @return int
     */
    public function getBindingType()
    {
        return ParameterType::BINARY;
    }

However I have added it in and I'm still not getting results back so I'll keep investigating.

warslett · 2020-11-21T12:14:33Z

adding getBindingType breaks other things so had to take it off.

This doesn't work either, still tried to use the string value as the parameter:

$query = $this->entityManager->createQueryBuilder()
            ->select('om')
            ->from(OrganisationMember::class, 'om')
            ->leftJoin('om.user', 'u')
            ->where('u.ulid = :ulid')
            ->setParameter('ulid', $user->getUlid())
            ->getQuery()
        ;

        $result = $query->getResult();

However this query executes correctly:

        $query = $this->entityManager->createQueryBuilder()
            ->select('om')
            ->from(OrganisationMember::class, 'om')
            ->leftJoin('om.user', 'u')
            ->where('u.ulid = :ulid')
            ->setParameter('ulid', $user->getUlid()->toBinary(), ParameterType::BINARY)
            ->getQuery()
        ;

        $result = $query->getResult();

But this is not intuitive. I would expect to be able to just filter by the association or at least join the association and filter by the ulid object.

jderusse · 2020-11-24T21:43:54Z

The issue is, when using Raw DQL (or building the query) Doctrine does not have the context, and does not know what is an Ulid. When you call setParameter without providing the $type argument, Doctrine try to guess the type, and, sadly, the logic is very simplistic (see https://github.com/doctrine/orm/blob/2.7/lib/Doctrine/ORM/Query/ParameterTypeInferer.php).
by default, Doctrine think an ulid is a string and then, cast the value to string (while the value is stored as binary).

When using the Repository, you don't have such issue, because doctrine know the class and know the type of the field

Several solution for you.

Help doctrine

->setParameter('ulid', $member->getUlid(), 'ulid')

Convert value to a value compatible with the type inferred by doctine

->setParameter('ulid', $member->getUlid()->toBinary())

Use Repository

$organisationMemberRepository->findOneBy(['user' => $user]);
$organisationMemberRepository->findOneBy(['user' => $user->getUlid()]);
$organisationMemberRepository->findOneByUser($user);

warslett · 2020-12-08T11:25:49Z

thanks @jderusse I've adopted ->setParameter('ulid', $member->getUlid(), 'ulid') in my own code this is the simplest.
It might be worth noting this in the documentation because the behaviour is inconsistent with how developers might be used to working with doctrine where they can set an entity as a parameter when filtering by association without having to do any joins. This won't work with ULID and it fails silently so you don't know your query doesn't work it just returns 0 results. I'll create a PR for the docs if I get a chance.

Some times it is necessary to use the querybuilder instead of using a repository. For example when using any tool that needs to alter the query before running it such as pagination libraries or tools that provide generic functionality for interacting with data such as admin bundles that might need to apply sorts and filters before the query is executed etc.

In the long term it would be great if Doctrine could support some way of registering parameter type mappings for objects. I will create a feature request if I get a chance

hdimo · 2021-02-10T21:29:03Z

In my case, for many to many I used this solution

...
// for the ManyToMany 
$ctoBinary = array_map(
   fn($category) => $category->getId()->toBinary(),
   $data->categories
);
$qb = $qb
        ->andWhere('c.id IN (:categories)')
        ->setParameter('categories', $ctoBinary,);
....

stof · 2021-02-11T11:38:55Z

well, Doctrine DBAL has a Connection_STR_ARRAY parameter for array parameters. But it indeed does not have a way to define parameters as being an array of a custom type.

Tofandel · 2022-09-23T12:43:34Z

Maybe something can be done on doctrine's ParameterTypeInferer class, we could add an interface that if specified the parameter can infer the type from an object's method, that way we don't need to specify 'ulid' every time and it's not a breaking change

Tofandel · 2022-10-28T09:26:00Z

Okay I just discovered that this is a major problem with no workarounds for eager loads, when using fetch: 'EAGER' on a relation with Ulid, the query uses base 32 instead of binary and returns no result. The eager load will thus not work and fall back to one query by entity to load, this can be a major performance bottleneck

Tofandel · 2022-10-28T09:34:55Z

Issue is open there on doctrine doctrine/dbal#4660

devDzign · 2024-04-05T15:17:35Z

Dear Community,

I'm reaching out regarding an ongoing issue on GitHub concerning eager loading with Symfony's ULID. After thorough investigation and attempts at resolution, it seems that no definitive solution has been found thus far.

I'm encountering the same problem with eager loading and ULID in Symfony, and I see that others have faced similar challenges.

I would greatly appreciate it if anyone who has successfully resolved this issue could share their insights or provide guidance on how to tackle it. Any assistance in formulating a solution would be immensely beneficial.Dear Community,

nicolas-grekas · 2024-04-05T15:55:06Z

This issue is closed, please open a new one for any follow up.

warslett added the Bug label Nov 21, 2020

xabbuh added the DoctrineBridge label Nov 21, 2020

colinodell mentioned this issue Nov 27, 2020

[DoctrineBridge] ORMQueryBuilderLoader not compatible with binary UUID columns #39207

Closed

jderusse mentioned this issue Dec 8, 2020

Add documentation about setParameter with UID symfony/symfony-docs#14676

Closed

nicolas-grekas closed this as completed Dec 10, 2020

nuil mentioned this issue Feb 15, 2021

[DoctrineBridge] Ulid is not converted when deleting a ManyToMany Relation #40199

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

[DoctrineBridge] Ulid is not converted to correct database type in queries #39135

[DoctrineBridge] Ulid is not converted to correct database type in queries #39135

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

[DoctrineBridge] Ulid is not converted to correct database type in queries #39135

[DoctrineBridge] Ulid is not converted to correct database type in queries #39135

Comments

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!