EntityFilterType fails to filter with related entities with UUID or ULID identifiers

[maxmishyn]

When we have entity where id is defined using one of Symfony\Component\Uid..... types, EntityFilterType is able to generate list, to render active fileter applied, but it faild to add filtering parameter correctly to the queryBuilder.

Example:

use Symfony\Component\Uid\Ulid;
class Product 
{
    #[ORM\Id]
    #[ORM\Column(type: UlidType::NAME, unique: true)]
    private Ulid $id;
    public function getId(): Ulid
    {
         return $this->id;
    }
}

class Batch
{
    #[ORM\ManyToOne]
    #[ORM\JoinColumn(nullable: false)]
    private ?Product $product = null;
}

We apply filter as usual:

            $builder
                ->addFilter('product', EntityFilterType::class, [
                    'form_options' => [
                        'class' => Product::class,
                        'choice_label' => 'name',
                    ],
                    'choice_label' => 'name',
                ]);

As a result doctrine tries to execure something like: SELECT count(*) AS sclr_0 FROM batch b0_ WHERE (b0_.product_id = '01JR5KAQXPAS26X4W9WPEV9407') AND (b0_.deleted_at IS NULL); and fails to find anything as product_id is binary and not string.

So far I was able to narrow it down to the create method in Kreyu\Bundle\DataTableBundle\Bridge\Doctrine\Orm\Filter\ParameterFactory class.

It creates parameters with Doctrine\ORM\Query\Parameter(), but ony 2 parameters being passed to the constructor. The third one is one of doctrine's binding types. Without if constructor calls ParameterTypeInferer::inferType($value) to determine the type, bus since it is object, it simply falls back to the string value.

[maciazek]

Currently it can be worked around by using "PRE_SET_PARAMETERS" event to set parameter type. I added specific example in my so-called "demo app":

https://github.com/maciazek/kreyu-data-table-bundle-demo/blob/604483185fa1b7be2af32b13167685531a5d30a4/src/DataTable/Type/Filter/FilterEventsDataTableType.php#L58C1-L65C6

[maxmishyn]

@maciazek, great! It works for me. Maybe some more straign forward solution would be better, but at theis moment I do not have any idea how to modify ParameterFactory to autodetect UUID typed identifiers as most of the autodetection is done anyway inside of Doctrine's code. Maybe dispatch PRE_SET_PARAMETERS event in some place there. On the other hand UUIDs are probably not the most comonly used types. Even in QueryBuilder we have to use ->toBinary instead of autodetecting type in paremeters. But it depends on @Kreyu 's vision how it should be done or maybe should not be done as it is definitely is not a "show-stopper"

[maxmishyn]

I was looking into one my older project and just realized that there is even more generic solution of it. I needed it to cover filtering requirements in Api-platform. I've decorated EntityManager with custom createQueryBuilder() method. It returns customized version of QueryBuilder with this setParameter implementation:

    public function setParameter($key, $value, $type = null): static
    {
        if (null === $type) {
            if ($value instanceof Ulid) {
                $type = Types::GUID;
            }

            if (is_object($value) && method_exists($value, 'getId')) {
                if ($value->getId() instanceof Ulid) {
                    $value = $value->getId();
                    $type = Types::GUID;
                }
            }
        }
        return parent::setParameter($key, $value, $type);
    }

[Kreyu]

Thanks for the report and for workarounds!

I'm thinking of improving the EntityFilterType a bit, even a little outside the scope of this issue:

• adding entity_class option,
• adding entity_identifier option (defaults to null, more on that later),
• renaming choice_label to entity_label.

Assuming I have a filter for Product entity with UUID identifier, I would define the filter like this:

$builder->addFilter('product', EntityFilterType::class, [
    'entity_class' => Product::class,
    'entity_label' => 'name',
])

The entity_class and entity_label would be used internally by the bundle, and automatically set as the default form's class and choice_label options.

Having entity_class will allow us to retrieve entity identifiers and their types (not sure if necessary):

$entityClassMetadata = $this->managerRegistry
    ->getManagerForClass($entityClass)
    ->getClassMetadata($entityClass);

$identifiers = $entityClassMetadata->getIdentifier();

// We can also retrieve type of field here, like:
// $entityClassMetadata->getTypeOfField('id');

There might be multiple identifiers. I think in that case, we should require providing specific identifier name via the entity_identifier option.

In ParameterFactory, we can retrieve the value of the selected entity's identifier, and if it is an instance of Symfony\Component\Uid\Uuid, we can convert it to binary format, like so:

use Symfony\Component\Uid\Uuid;
use Doctrine\DBAL\ParameterType;

// Default parameter type is null, so Doctrine will automatically determine it.
$parameterType = null;
$value = $data->getValue();

if ($value instanceof Uuid) { 
    $parameterType = ParameterType::BINARY;
    $value = $value->toBinary();
}

I'm not sure if there would be a case, where someone want to prevent this behavior, since it doesn't work currently.

The filter type definition would look cleaner, and UUIDs would work out of the box.