In my Symfony2 repository, I’d like to get objects from a schedule table, that have started, but not finished yet. The interval, within the objects should be encountered as ‘not finished’, should be passed as a variable.
Using plain SQL, it works like this:
SELECT * FROM slots rbs WHERE rbs.rundate = '2012-08-13' AND rbs.runtime <= '11:05:00' AND '11:05:00' <= rbs.runtime + interval '300 seconds' ORDER BY rbs.rundate DESC, rbs.runtime DESC
Can I achieve the same with DQL / Query Builder?
This is what I have so far:
$qb = $this->createQueryBuilder('rbs'); $qb->where( $qb->expr()->andX( $qb->expr()->eq('rbs.rundate', ':date'), $qb->expr()->lte('rbs.runtime', ':time'), 'rbs.runtime + interval 300 seconds >= :time' ) ) ->orderBy('rbs.rundate', 'DESC') ->addOrderBy('rbs.runtime', 'DESC') ->setParameter('date', date('Y-m-d')) ->setParameter('time', date('H:i:s'))
But this returns the following error:
[DoctrineORMQueryQueryException] [Syntax Error] line 0, col 139: Error: Expected =, <, <=, <>, >, >=, !=, got '300'
I found that ‘interval’ is not supported by Doctrine2 / DQL, which is also mentioned here.
Any suggestions on how to accomplish this with Doctrine2’s Query Builder or DQL (and passing the interval as variable)?
Advertisement
Answer
As far as I know, Interval is not ported in Doctrine. The workaround I found is to work directly on the DateTime I pass as a param (here, I’d like to use interval of 2 days, passed through Datetime):
public function findOngoingPublicEvents() { return $this->createQueryBuilder('e') ->where('e.isActive = 1') ->andWhere('e.isPublic = 1') ->andWhere('e.begin <= :begin') ->andWhere('e.end >= :end') ->orderBy('e.id', 'ASC') ->setParameter('begin', new DateTime('+2 days')) ->setParameter('end', new DateTime('-2 days')) ->getQuery() ->execute(); }