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

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