原则 2.2 DQL 日期组

Question

我正在尝试执行此查询：

public function historyLoginAction() {
    $em    = $this->get('doctrine.orm.entity_manager');
    $em->getConfiguration()->addCustomDatetimeFunction('DATE', 'TBUserBundleDQLFunctionsDateFunction');
    $dql   = "SELECT COUNT(u.id),u.lastLogin, DATE(u.lastLogin) AS groupDate FROM UserBundle:User u GROUP BY groupDate ORDER BY u.lastLogin DESC";
    $query = $em->createQuery($dql);
    $paginator  = $this->get('knp_paginator');
    $pagination = $paginator->paginate(
        $query,
        $this->get('request')->query->get('page', 1)/*page number*/,
        12/*limit per page*/
    );

日期函数：

<?php
namespace TBUserBundleDQLFunctions;
use DoctrineORMQueryASTFunctionsFunctionNode;
use DoctrineORMQueryLexer;
use DoctrineORMQuerySqlWalker;
use DoctrineORMQueryParser;
class DateFunction extends FunctionNode
{
    private $arg;
    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf('DATE(%s)', $this->arg->dispatch($sqlWalker));
    }
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->arg = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

根据这些页面...

在带有 DQL 的 WHERE 子句中使用 DATE（） 函数

原则：按日期范围分组

http://www.doctrine-project.org/jira/browse/DDC-1236

当我在$paginator之前var_dump查询结果时

    var_dump($query->getResult());die;

我得到了我的输出（所以 DQL 很好，对吧？

array
  0 => 
    array
      1 => string '1' (length=1)
      'lastLogin' => 
        object(DateTime)[968]
          public 'date' => string '2014-01-11 16:31:46' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Berlin' (length=13)
      'groupDate' => string '2014-01-11' (length=10)
  1 => 
    array
      1 => string '1' (length=1)
      'lastLogin' => 
        object(DateTime)[988]
          public 'date' => string '2013-10-15 21:05:31' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Berlin' (length=13)
      'groupDate' => string '2013-10-15' (length=10)
  2 => 

但是当我将查询传递给分页器时，我得到：

Notice: Undefined index: groupDate in /vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php line 2197

用：

    "knplabs/knp-paginator-bundle": "2.3.3",
    "knplabs/knp-components": "1.2.4"

请问我错过了什么？

Answer 1

试试这个。以下是一些解决方案和类似问题http://www.programmingrelief.com/4151523/Symfony2-Doctrine-Group-BySymfony2 学说 分组依据

SELECT COUNT(u.id),
u.lastLogin,
DATE(u.lastLogin) AS groupDate 
CAST(u.lastLogin as date) AS HIDDEN groupDateGrp 
FROM UserBundle:User u 
GROUP BY DATE(groupDateGrp) 
ORDER BY u.lastLogin DESC

Answer 2

我的问题已经解决了。我的查询结果与 KNP 不兼容，我想我做了什么？

$pagination = $paginator->paginate(
    $query,

更改为

$result = $query->getResult();    
$pagination = $paginator->paginate(
    $result,