drox90250557
drox90250557
2017-03-15 07:07
浏览 13
已采纳

使用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

图片转代码服务由CSDN问答提供 功能建议

我需要你的帮助,我将如何将以下Mysql转换为Doctrine。 \ n

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

我试过这个:

  $ 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); 
 <  / code>  
 
 

(假设已提供 $ start $ end

我有以下错误:

  {
“type”:“http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html”  ,
“标题”:“内部服务器错误”,
“状态”:500,
“详细信息”:“[语法错误]第0行,第57行:错误:预期的已知功能,得到'最少'”  n} 
   
 
 

我该怎么做?

这是我生成的问题: \ n

Mysql查询,以确定日期时间间隔内是否包含给定的日期时间

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douniuta4783
    douniuta4783 2017-03-15 07:30
    已采纳

    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

    点赞 评论
  • du1068
    du1068 2018-03-26 21:46

    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);
    
    点赞 评论

相关推荐