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. ?

Answer 1

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.

Answer 2

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(); }