Symfony2 doctrine GROUP BY

Question

I create a MONTH function to extract month of a date.

I want to GROUP BY the result with these query:

SELECT MONTH(p.publicationDateStart) AS archive 
FROM ApplicationSonataNewsBundle:Post p 
GROUP BY archive

But it gives me error:

An exception has been thrown during the rendering of a template 
("Notice: Undefined index: archive in 
/home/kiddo/Code/apache2/work/ibctal.dev/vendor/doctrine/orm/lib/Doctrine/ORM/Query
/SqlWalker.php line 2197") in ApplicationSonataNewsBundle:Post:archive.html.twig 
at line 10.

How can I fix this problem.

For reference I found this information of doctrine's commit: https://github.com/doctrine/doctrine2/commit/2642daa43851878688d01625f272ff5874cac7b2:

EDIT :

Here is the Controller Code

namespace Application\Sonata\NewsBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller; use Doctrine\ORM\Query\ResultSetMapping;

class SidebarController extends Controller
{
    public function archiveAction()
    {
        $em = $this->getDoctrine()->getManager();
        $dql = "SELECT 
                    MONTH(p.publicationDateStart) AS archive, 
                    p.publicationDate AS HIDDEN archiveDate
                FROM 
                    ApplicationSonataNewsBundle:Post p 
                GROUP BY 
                    MONTH(archiveDate)";
        $query = $em->createQuery($dql);
        $paginator = $this->get('knp_paginator');
        $pager = $paginator->paginate($query, $this->get('request')->query->get('page', 1), 10);

        return $this->render('ApplicationSonataNewsBundle:Sidebar:archive.html.twig', array(
            'archives' => $pager
        ));
    } }

And FYI, I use 2.3.1-DEV version

Can you do a `GROUP BY MONTH(p.publicationDateStart)` instead? — lifo, Jan 02 '13 at 11:58
Tried it already. Got this error: `An exception has been thrown during the rendering of a template ("[Semantical Error] line 0, col 97 near 'MONTH(p.publ': Error: Cannot group by undefined identification or result variable.") in ApplicationSonataNewsBundle:Post:archive.html.twig at line 10. ` — Kiddo, Jan 02 '13 at 12:16
possible duplicate of [Query-builder in Symfony2](http://stackoverflow.com/questions/13543937/query-builder-in-symfony2) — Steve Tauber, Nov 13 '13 at 17:56

score 0 · Answer 1 · answered Jan 02 '13 at 13:26

0

I had to use a similar workaround for this same purpose, check out my solution:

SELECT 
    MONTH(p.publicationDateStart) as archive, 
    CAST(p.publicationDateStart as date) AS HIDDEN archiveDate 
FROM
    ApplicationSonataNewsBundle:Post p
GROUP BY 
    MONTH(archiveDate)

answered Jan 02 '13 at 13:26

phpisuber01

7,585
3
22
26

Got this error: `An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 91: Error: Expected known function, got 'CAST'") in ApplicationSonataNewsBundle:Post:archive.html.twig at line 10. ` – Kiddo Jan 02 '13 at 13:38
@kiddo, I would make your query 1 line and make sure your spacing is correct, then run your DQL and var_dump the result. Make sure its the query breaking. It appears from the error that its a template error. – phpisuber01 Jan 02 '13 at 13:54
I made it one line, and still the same error. Cannot var_dump, because error appeared before the result. I think the error means doctrine doesn't recognize CAST as a function – Kiddo Jan 02 '13 at 14:05

Symfony2 doctrine GROUP BY

1 Answers1

Linked