php - 从 MySQL 查询结果创建关联数组 - Doctrine2

Question

我正在做一个 Symfony2 项目，我需要使用一个日历插件来传递一些值，如下所示:

JS

 $.ajax({
   type: 'POST',
   url: Routing.generate('example'),
   data: {},
   dataType: 'json',
   success: function(response) {

    $('#calendar').eCalendar({
    ...
    events: [{
       title: 'Example Title 1', 
       description: 'Description 1.', 
       datetime: new Date(2016, 11, 30) 
    },
    {
       title: 'Example Title 2', 
       description: 'Description 2.', 
       datetime: new Date(2016, 10, 23), 
    }]
  });
 }
})

这些事件数据可以通过ajax传递如下:

JS

 $.ajax({
   type: 'POST',
   url: Routing.generate('example'),
   data: {},
   dataType: 'json',
   success: function(response) {

    for (var i = 0; i < response.data.length; i = i + 1) {
      str=response.data[i].datetime.date;

      year=str.substring(0,4);
      month=str.substring(5,7);
      day=str.substring(8,10);

      var myDate = new Date(year,month-1,day);
      response.data[i].datetime = myDate;
    }

    $('#calendar').eCalendar({
    ...
    events: response.data
  });
 }
})

PHP

public function exampleAction()
{

 $arr = array(array("title" => 'Example Title 1',"description" => "Description 1.","datetime"=>new \DateTime("now")),
        array("title" => 'Example Title 2',"description" => "Description 1.","datetime"=>new \DateTime("now"))
    );

    return new JsonResponse(array(
        'data' =>$arr,
        'success' => true), 200);
}

到目前为止一切顺利。但现在我需要从数据库中的下表中获取该数据:

id  title               description     datetime                   category     hour    
1   Example Title 1     Description 1.  2016-12-21 00:00:00     general     all day     
2   Example Title 2     Description 2.  2016-12-21 00:00:00     general     09:00 

在我的 Repository 类中，我使用以下代码进行查询:

public function findEvents($category) //$category has the searched value passed from the controller.
{
return $this->getEntityManager()->createQuery(
    'SELECT e FROM BackendBundle:Events e WHERE e.category=:category ORDER BY e.datetime ASC, e.hour ASC')
    ->setParameters(array(
        'category' => $category))
    ->getResult();
}

但现在我不知道如何才能得到一个只与某些列的值关联的数组，就像这个例子:

 $arr = array(array("title" => 'Example Title 1',"description" => "Description 1.","datetime"=>new \DateTime("now")),
        array("title" => 'Example Title 2',"description" => "Description 1.","datetime"=>new \DateTime("now"))
    );

Answer 1

最后感谢@Garry，我能够使用以下代码解决我的问题:

public function findEvents($category) //$category has the  searched value passed from the controller.
{
  return $this->getEntityManager()->createQuery(
   'SELECT CONCAT(CONCAT(e.title, ' '), e.date) AS title, e.description, e.datetime FROM BackendBundle:Events e WHERE e.category=:category ORDER BY e.datetime ASC, e.hour ASC')
   ->setParameters(array(
    'category' => $category))
   ->getResult();
}

除了只获取我需要的三列的值外，我还能够将日期添加到标题(我没有在问题的前面指出)。