Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use 'interval' in Doctrine2 Query Builder

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:

[Doctrine\ORM\Query\QueryException]                                                 
[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)?

like image 354
mediafreakch Avatar asked Aug 13 '12 14:08

mediafreakch


3 Answers

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();
}
like image 104
guillaumepotier Avatar answered Oct 18 '22 23:10

guillaumepotier


If you want to use INTERVAL (in Doctrine 2, DQL) on mysql comumn field, You can use as below,

$qb->andWhere("DATE_ADD(pv.myDAte,48,'hour') >= UTC_TIMESTAMP()");

It will print SQL as below,

...... DATE_ADD(p0_.appointment_date, INTERVAL 48 HOUR) >= UTC_TIMESTAMP() .....
like image 9
Kiran Avatar answered Oct 18 '22 22:10

Kiran


@Kiran write only about DATE_ADD, but you can also use DATE_SUB

$qb->andWhere("DATE(a2_.updatedAt) = DATE_SUB(CURRENT_DATE(), 6, 'day')");

It is equivalent of SQL:

DATE(a2_.updatedAt) = DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)
like image 3
Serhii Popov Avatar answered Oct 18 '22 23:10

Serhii Popov