使用LEAST和GREATEST函数将SQL转换为Doctrine

I need your help on how am I going to convert the following Mysql to Doctrine.

select * from calendar_data as c where LEAST(c.end, end) - GREATEST(c.start, start) > 0;

I tried this one:

$qb = $em->createQueryBuilder();
$query = $qb->select('items')
                ->from('\Admin\Entity\CalendarData','items')
                ->where('LEAST(items.end, :end) - GREATEST(items.start, :start) > 0')
                ->setParameter('start',$start)
                ->setParameter('end', $end);

(Given that $start and $end is already provided)

and I have the following error:

{
 "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html",
 "title": "Internal Server Error",
 "status": 500,
 "detail": "[Syntax Error] line 0, col 57: Error: Expected known function,   got 'LEAST'"
}

How should I do that?

This is the generated sql to my question:

Mysql query to determine if the given datetime is included in the datetime interval

There is no LEAST and GREATES functions in Doctrine. you can rewrite your query to achieve the same result or use ResultSetMapping, like

use Doctrine\ORM\Query\ResultSetMapping;

$start = '';
$end = '';
$rsm = new ResultSetMapping();
$rsm->addEntityResult('CalendarData', 'c');
$rsm->addFieldResult('c', 'id', 'id');
$rsm->addFieldResult('c', 'start', 'start');
$rsm->addFieldResult('c', 'end', 'end');

$query = $this->_em->createNativeQuery('select * from calendar_data where LEAST(c.end, ?) - GREATEST(c.start, ?) > 0;', $rsm);
$query->setParameter(1, $end);
$query->setParameter(2, $start);

$calendarData = $query->getResult();

Read about native sql in doctrine here

Use Beberlei's DoctrineExtensions to extend DQL with many more MySQL functions such as GREATEST and LEAST.

1. Install the library => composer require beberlei/DoctrineExtensions

2. Add the functions you need to your config.yml

   doctrine:
       orm:
           dql:
               string_functions:
                   least: DoctrineExtensions\Query\Mysql\Least
                   greatest: DoctrineExtensions\Query\Mysql\Greatest
   

After installing and linking, your first try at converting the code should work as expected:

$qb = $em->createQueryBuilder();
$query = $qb->select('items')
                ->from('\Admin\Entity\CalendarData','items')
                ->where('LEAST(items.end, :end) - GREATEST(items.start, :start) > 0')
                ->setParameter('start',$start)
                ->setParameter('end', $end);