查找总和并按月分组从db获取的数据

I know this question has became a cliche, but there were no answers regarding how to solve this using doctrine:

so here is my doctrine query of fetching timestamp and amount from the table:

$q = $repo->createQueryBuilder('s')
            ->select('s.timestamp','s.amt')
            ->where('s.disId=:id')
            ->setParameter('id', $cid)
            ->getQuery()
            ->getArrayResult();

currently I am fetching all the individual rows from the table, I want the output to sum up amt(amount) month wise and record displayed accordingly.

Thanks for your help

If your date is stored as timestamp, then it is not possible to do directly, because Doctrine ORM does not currently support functions like Mysql's MONTH(), which you need to use in GROUP clause. Possible solutions:

1. install bundle that adds these functions, for example https://github.com/beberlei/DoctrineExtensions and use functitons MONTH() and FROM_UNIXTIME() from there

2. use doctrine's native query: http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/native-sql.html

3. use DBAL layer of doctrine: http://doctrine-orm.readthedocs.io/projects/doctrine-dbal/en/latest/

For you simplest would be probably option 3, code could be something like:

$em = $this->get("doctrine")->getManager();
$conn = $em->getConnection();
$sql = "SELECT MONTH(FROM_UNIXTIME(s.timestamp)) as mon, SUM(s.amt) as total FROM tablename s WHERE s.dis_id = ? GROUP BY MONTH(FROM_UNIXTIME(s.timestamp)) ORDER BY 1";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $cid);
$stmt->execute();
$all_rows = $stmt->fetchAll();
foreach ($all_rows as $row) {
    echo "Month: {$row["mon"]}, Total: {$row["total"]} 
";
}

try this :

$q = $repo->createQueryBuilder('s')
        ->select('s.timestamp','sum(s.amt)')
        ->where('s.disId=:id')
        ->groupBy('s.month')//your month field name of database
        ->setParameter('id', $cid)
        ->getQuery()
        ->getArrayResult();

By using groupBy you will get result.