Doctrine: query array type

Question

Doctrine can automatically serialize simple PHP array values (see https://www.doctrine-project.org/projects/doctrine-dbal/en/2.10/reference/types.html#array-types).

Example

<?php
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="my_table")
 */
class MyTable {
    /**
     * @var array
     *
     * @ORM\Column(name="my_array", type="json", nullable=true)
     */
    private $myArray;
}

Example in database

|   my_array    |
|---------------|
|'["FOO","BAR"]'|

Question

Is there a way to query these values without some sort of ugly regex directly by DQL or with query builder?

$queryBuilder
    ->andWhere(":searchParam IN myTable.myArray")
    ->setParameter('searchParam', 'FOO');

Side note: I could always wrap the searchParam and search with LIKE key word. However I am looking for an alternative solution.

What database system are we talking about? For example, PostgreSQL has JSONB type, which can be nicely queried. I'm not familiar with MySQL but there's also JSON data type. — Furgas
– Furgas, Commented Jul 31, 2018 at 14:16
PostgresSql witch does has its own JSON type. But I am limited with what can Doctrine and its DQL do. — Wolfer
– Wolfer, Commented Jul 31, 2018 at 14:45
Right, that's why I don't use Doctrine :) Nevertheless, I believe that you can use raw SQL with Doctrine, but I'm not an expert on this. — Furgas
– Furgas, Commented Jul 31, 2018 at 14:58

Denis Alimov · Accepted Answer · 2018-07-31 14:05:10Z

1

No, there is no such way. references: first second

answered Jul 31, 2018 at 14:05

Denis Alimov

2,9011 gold badge21 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wolfer Over a year ago

Unfortunately true :-\ I am marking this as correct answer until Doctrine implements this.

Collectives™ on Stack Overflow

Doctrine: query array type

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related