How to use Doctrine Criteria to filter out array properties?

Question

I'm adding a virtual property within a Symfony entity class. This property shall be computed based on another table data - specifically on a column that is of the Doctrine array type.

class RelatedEntity
{

    /* ... */

    /**
     * @ORM\Column(type="array")
     */
    protected $type;

The point is I would like to use Doctrine Criteria for this as it's supposed to be optimized on SQL level. So I did this:

public function getCreated()
{
    $criteria = Criteria::create()->where(Criteria::expr()->contains('type', 'create'));
    $relatedEntity = $this->getRelatedEntities()->matching($criteria);

    if (!$relatedEntity) {
        return null;
    }

    return $relatedEntity->getTimestamp();
}

But I get an empty result set. Even though Doctrine is building a correct SQL statement, which works when I type it manually into the PostgreSQL database.

...WHERE type LIKE '%create%'

What is wrong with this approach and how can it be solved? Right now I did the trick with the ArrayCollection filter method, but it loads all related entities I don't need.

Thank you for any ideas.

EDIT: This is not a duplicate of the mentioned question as I cannot use EntityManager or EntityRepository inside an entity. I need to use Criteria, so the solution proposed in the question doesn't work for me.

Answer 1

Check the results of getRelatedEntities()

Depending on how this collection was created, any one of several things may be happening. In particular, it may be using entity aliases, or may not be returning any which match your Criteria.

1. Collection populated from an aliased entity (i.e.: via a QueryBuilder join/select).
   • If getRelatedEntities is populated by Doctrine via QueryBuilder, you've likely aliased the Entities.
   • EX.: $queryBuilder->addSelect('thing')->leftJoin('root_alias.entity', 'thing')
   • In such a case, the Criteria must use the alias:
     • Criteria::expr()->contains('thing.type', 'create')
2. No matches for Criteria.
   • Dump your collection before filtering it, this could be a simple case of your query having already filtered out any potential matches.

Test your Criteria

All things considered, without any clue as to the structure of the collection you're trying to filter, we can only assess your criteria. Thus, test your criteria, and check the contents of the collection you are attempting to filter.

$criteria = Criteria::create()->where(Criteria::expr()->contains('type', 'create'));

$collection = new ArrayCollection([
    [
        'key' => 1,
        'type' => 'somethingcreatesomething',
    ],
    [
        'key' => 2,
        'type' => 'abra',
    ],
    [
        'key' => 3,
        'type' => 'cadabra',
    ],
    [
        'key' => 4,
        'type' => 'alacreate',
    ],
]);

dump($collection->matching($criteria));

Result

Doctrine\Common\Collections\ArrayCollection {#2536
  -elements: array:2 [
    0 => array:2 [
      "key" => 1
      "type" => "somethingcreatesomething"
    ]
    3 => array:2 [
      "key" => 4
      "type" => "alacreate"
    ]
  ]
}