Doctrine Query count() and groupBy() date

Question

I need to find the total number of logins per day, but how would I select count() and group by day in DQL? I'm using Doctrine 2.3.

public function getLoginCount()
{
    return $this->createQueryBuilder('i')
            ->select('i') // and count(*)
            ->groupBy('i.timestamp') // group by day
            ->getQuery()
            ->execute()
        ;
}

I need something like this:

Date | count
2013-01-01 | 6
2013-01-02 | 7
2013-01-03 | 3

Answer 1

From help on the Doctrine IRC channel you need to create a custom DQL function.

Example: https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Day.php

Docs: http://www.doctrine-project.org/blog/doctrine2-custom-dql-udfs.html

Answer 2

A bit late for OP, but maybe someone will find it handy. I was able to achieve that with the DQL query bellow:

    $dql = '
        SELECT
            SUBSTRING(i.timestamp, 1, 10) as date,
            COUNT(i) as count
        FROM Entity i
        GROUP BY date
    ';
    $query = $entityManager->createQuery($dql);
    return $query->getResult();

I think similar should be doable with Doctrine query builder.