Doctrine 2.2 DQL DATE GROUP BY

Question

I am trying to execute this query:

public function historyLoginAction() {
    $em    = $this->get('doctrine.orm.entity_manager');
    $em->getConfiguration()->addCustomDatetimeFunction('DATE', 'TB\UserBundle\DQLFunctions\DateFunction');

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

DateFunction:

<?php

namespace TB\UserBundle\DQLFunctions;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\Parser;

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);
    }
}

According to these pages...

Use a DATE() function in a WHERE clause with DQL

Doctrine: Group by date ranges

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

When i var_dump the query result before $paginator

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

I get my output (so the DQL is good right?)

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 =>

But when i pass the query to paginator i am getting:

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

Using:

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

What am i missing please?

score 1 · Answer 1 · edited May 23 '17 at 12:17

1

Try this. Here are some solutions and similar issue http://www.programmingrelief.com/4151523/Symfony2-Doctrine-Group-By Symfony2 doctrine GROUP BY

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

edited May 23 '17 at 12:17

Community

1
1

answered Jan 12 '14 at 20:49

Abhik Chakraborty

44,654
6
52
63

can you check if your query is returning some data ? – Abhik Chakraborty Jan 12 '14 at 20:51
Another issue you have UserBundle:User I suppose it should be just User, I have updated the answer. – Abhik Chakraborty Jan 12 '14 at 20:54
No, it should be UserBundle:User i write it like this in every DQL query. + i am getting error when i tried just User as you suggested. – Lukas Lukac Jan 12 '14 at 20:55
ok just echo the query and see what it gives without Hidden and with UserBundle, something as echo $dql; – Abhik Chakraborty Jan 12 '14 at 20:58
object(Doctrine\ORM\Query)[918] private '_state' => int 2 private '_dql' => string 'SELECT COUNT(u.id),u.lastLogin, DATE(u.lastLogin) AS groupDate FROM UserBundle:User u GROUP BY groupDate ORDER BY u.lastLogin DESC' (length=130) – Lukas Lukac Jan 12 '14 at 21:03
Your query looks good, and using the UserBundle:User should read the table as defined. I have created a test http://www.sqlfiddle.com/#!2/03a65/1 with your query and looks good. Can you check historyLoginAction() if the query is getting executed, looks like its not getting executed and while trying to get data for index "groupDate" its throwing an error. – Abhik Chakraborty Jan 12 '14 at 21:22

score 0 · Accepted Answer · edited Aug 16 '15 at 05:57

0

My Problem has solved. My query result was not compatible with KNP i guess so what i did ?

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

Changed to

$result = $query->getResult();    

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

edited Aug 16 '15 at 05:57

Ahmad Sajid

191
3
15

answered Jan 16 '14 at 14:24

Lukas Lukac

7,766
10
65
75

Doctrine 2.2 DQL DATE GROUP BY

2 Answers2