using distinct on a entity that has a json field

[leberknecht]

Hi!

I have an entity that has a json field:

// MyClass.php
class MyClass
{
    #[ORM\Id]
    #[ORM\Column(type: 'uuid', unique: true)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: UuidGenerator::class)]
    public $id;
    
    #[ORM\Column(type: 'json', nullable: true)]
    private array|null $info = null;
[...]

Things are running mostly smoothly, but when i fire a distinct query, it creates problems:

// MyClassRepository.php
    public function findSomething(int $limit): array
    {
        return $this->createQueryBuilder('i')
            ->distinct(true)
            ->setMaxResults($limit)
            ->getQuery()->getResult();
    }    

When i run this im getting

Doctrine\DBAL\Exception\DriverException : An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR:  could not identify an equality operator for type json                                          ^

The created query looks like this:

SELECT DISTINCT [...]  e0_.info AS info_22 FROM [...] WHERE e1_.id IS NULL LIMIT 500

So what i would need is basically to somehow exclude the json field from the distinct columns. Is there any way to do that?

Note: simplified from real code to illustrate the problem. I can make a real-reproducable snippet if that helps)

[leberknecht]

I could of course split that into two parts, first query with restriction to the id:

$ids - $this->createQueryBuilder('i')
            ->select('i.id')
            ->distinct(true)
            ->setMaxResults($limit)
            ->getQuery()->getResult();
 return $this->createQueryBuilder('i')->where('i.id in (:list)')->setParameter('list', $ids);

that would do the trick i guess

[derrabus]

Yes.