How do I use a complex criteria inside a doctrine 2 entity's repository?

Question

Lets say I have a table that holds information about festivals.
Each festival has a start and end date.

I want to select all the festivals that are live (that happen) on a given date.

Meaning, I want to select all the festivals that their start date is before or on a given date, and that their end date is after or on a the same given date.

So I went on to the repository class of the festival entity, and created a method to do just that.
But the criteria argument "findBy" expects is an array, which all the examples only treat as a simple criteria (e.g. "array('name' => 'billy')" will select all the rows that have the value billy in their name column), which uses only the comparison operator.

How can I use other operators such as

>, <, !=, IN, NOT IN, LIKE    

and etc. ?

Solution 1

You'll need to write your own query (probably using DQL) if you want something that specific. I believe the built in "findBy" methods are more for just grabbing objects quickly if you have less specific criteria. I don't know your entity names or where they are stored. Could be something like this as a function in your Festival Repository.

public function findActiveFestivals($start, $end)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('f')
        ->from('Festival', 'f')
        ->where('f.start >= :start')
        ->andWhere('f.end <= :end')
        ->setParameters(array('start' => $start, 'end' => $end));

    return $qb->getQuery()->getArrayResult();
}

Solution 2

Doctrine 2.3 added a matching() method that lets you use Criteria.

The example by Jeremy Hicks may be written like this (note, this returns an ArrayCollection instead of an array).

public function findActiveFestivals($start, $end)
{
    $expr = Criteria::expr();
    $criteria = Criteria::create();
    $criteria->where($expr->gte('start', $start));
    $criteria->andWhere($expr->lte('end', $end);
    return $this->matching($criteria);
}

Personally, I wouldn't use andWhere here, and use a few more lines to improve readability, like this:

public function findActiveFestivals($start, $end)
{
    $expr = Criteria::expr();
    $criteria = Criteria::create();
    $criteria->where(
      $expr->andX(
        $expr->gte('start', $start),
        $expr->lte('end', $end)
      )
    );
    return $this->matching($criteria);
}

Using an IN clause is very simple.

public function findFestivalsByIds($ids)
{
    $expr = Criteria::expr();
    $criteria = Criteria::create();
    $criteria->where($expr->in('id', $ids));
    return $this->matching($criteria);
}

The Criteria class is in Doctrine's not-really-ORM-or-DBAL Common's namespace, like their ArrayCollection (which has supported Criteria longer than EntityRepository).

Its meant to be a decoupled way for non-repository code to create sophicated criteria. So it should be fine to use this class outside of the repository. QueryBuilder supports Criteria recently as well. So even when building more sophisticated queries that require QueryBuilder, you can use Criteria to give the non-database code flexibility in what it requests.

Solution 3

thats not the answer for Doron question doctrine have entity repository that not make us using query at all...

$this->em->getRepository($this->entity)->findBy(array $criteria);

but what did he asked is how to complex operator in array $criteria normal format of array $criteria is array('field'=> $value);

Solution 4

I have had the same problem a while back, where my Doctrine repositories became very ugly due to complex queries. I also had to convert Yii ActiveRecord (with Criteria objects) to Doctrine, and Doctrine did not have Criteria objects at the time.

I found a blogpost by Benjamin Eberlei which has an interesting solution to this problem based on the specification pattern.

It gives you the ability to defer the manipulation of the Query Builder object to other classes.

$spec = new AndX(
    new Equals('ended', 0),
    new OrX(
        new LowerThan('endDate', new \DateTime()),
        new AndX(
            new IsNull('endDate'),
            new LowerThan('startDate', new \DateTime('-4weeks'))
        )
    )
);

return $this->em->getRepository('Advertisement')->match($spec)->execute()

Furthermore you can compose 2 or more classes together, which creates nice reusable building blocks:

public function myQuery(User $user)
{
    $spec = new AndX(
        new ExpiredAds(),
        new AdsByUser($user)
    );

    return $this->em->getRepository('Advertisement')->match($spec)->execute();
}

In this case ExpiredAds() and AdsByUser() contain a structure like in the first code example.

If you think that solution would work for you, let me suggest two libraries which you can install through composer:

• Happyr\Doctrine-Specification
• RikBruil\Doctrine-Specification (disclaimer: I'm the author of this one)

View more solutions

Comments

[Doron]

Just so you know - when calling $this->createQueryBuilder() from inside the repository class, you get a query builder with the table and alias already configured

[David]

that's not a real answer for a question asked

[renoirb]

If that's impossible. I assume it is the proper answer... rely on a QueryBuilder manipulation.

[Kevin Bond]

In doctrine/orm 2.5, EntityRepository::matching will return LazyCriteriaCollection to allow doing a count without fetching all rows (github.com/doctrine/doctrine2/pull/882)

[Aleksei Akireikin]

Could someone explain why Criteria::create() used instead of new Criteria()?

[Diego Ferri]

Because create calls return new static(), that is PHP late static binding.