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)?
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();
}
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() .....
@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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With