Collections\Criteria::expr(), isNotNull and notLike

Question

I am using Doctrine\Common\Collections\Criteria::expr() (NOT the query builder expression).

It seems that isNotNull() and notLike() operators are not implemented in this class.

What is the best way to do isNotNull() and notLike() in this case?

Answer 1

Criteria IS NOT NULL

In doctrine/orm ^2.4 or doctrine/collections ^1.2 to have an is not null that acts like a Criteria::expr()->isNotNull('field') you can use

$criteria = Criteria::create();
$expr = $criteria::expr();
$collection->matching($criteria->where($expr->neq('field', null)));

This matches the same way the expression builder creates Expr::isNull but changes the comparison operator to Comparison::NEQ via:

return new Comparison($field, Comparison::EQ, new Value(null));

Which is then checked for by the QueryExpressionVisitor and BasicEntityPersister, used to build the query as Expr:isNotNull.

case Comparison::NEQ:
    if ($this->walkValue($comparison->getValue()) === null) {
        return $this->expr->isNotNull($this->rootAlias . '.' . $comparison->getField());
    }

---

Criteria LIKE and NOT LIKE

For the Criteria::expr()->like() functionality, Criteria::expr()->contains('property', 'value') is the equivalent of the SQL property LIKE %value%. However it does not allow for changing to value% or %valuebut a pull request (as of 2.5.4) with the proposed startsWith and endsWith methods that has been merged with master - so may be released with 2.5.5.

Unfortunately for Criteria::expr()->notLike() and the other LIKE variants, the \Doctrine\Common\Collections\ExpressionBuilder used by Criteria does not support them.

Additionally if the comparison operator is not defined (such as Comparison::CONTAINS), an error is thrown by the QueryExpressionVisitor and BasicEntityPersister, which prevents manually defining your own Comparison functionality.

https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/working-with-associations.html#filtering-collections

---

Custom Repository Alternative

The best alternative is to use a custom repository and the DBAL query builder expressions instead to substitute the desired functionality.

Using a custom entity repository to filter the result set will prevent a full-table read of your collections and improve speed when using caching.

https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/working-with-objects.html#custom-repositories

---

Collection Filter Alternative

An alternative is using filter to retrieve a specific subset of objects within a collection.

https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#the-expr-class

class MyEntity
{
    public function getCollectionFieldNotLike($value)
    {
        return $this->getCollection()->filter(function($a) use ($value) {
            return (false === stripos($a->getField(), $value));
        });
    }

    public function getCollectionFieldLike($value)
    {
        return $this->getCollection()->filter(function($a) use ($value) {
            return (false !== stripos($a->getField(), $value));
        });
    }
}

$entity->getCollectionFieldNotLike('value');
$entity->getCollectionFieldLike('value');

For procedural syntax combination of both on a repository.

$criteria = Criteria::create();
$expr = $criteria::expr();
$criteria->where($expr->neq('field', null));
$collection = $entityManager->getRepository('app:MyEntity')->matching($criteria);
$collectionNotLike =  $collection->filter(function($a) {
    return (false === strpos($a->getField(), 'value'));
});

Keep in mind, as stated above, this will force a full-table read on the collection, since it will need to retrieve the records in order to be able to filter the results.