How can I get the MONTH name in a DQL query?

Question

I have a query to get the number of insertions grouped by months. I have beberlei Doctrine extensions already installed and working, but the array return have the month number as key. How will be used in a chart line, I want to show the month name instead of month number.

What is the best way to do this?

This the query:

//get and format the current year
    $date = new \DateTime();
    $year = $date->format('Y');
    //create query
    $qb =  $this->_em->createQueryBuilder();
    $qb->select(['MONTH(p.createdAt) as month', 'count(ccp) as insertions'])
        ->from(CareConnectPatient::class, 'ccp')
        ->join('ccp.person', 'p')
        ->where('YEAR(p.createdAt) = :year')->setParameter('year', $year);
    $qb->groupBy('month');

    return $qb->getQuery()->getScalarResult();

And I do that with that result:

`$countMonths = count($cCPatientGroupedByMonth);
        for ($count = 0; $count<$countMonths; $count++){
            $arrayKeyAsMonth[$cCPatientGroupedByMonth[$count]['month']] = $cCPatientGroupedByMonth[$count]['insertions'];
        }`

What gives me something like this:

array:3 [
  4 => "1",
  8 => "2",
  9 => "2"
]

I what to know the best way to make this appear as:

array:3 [
  april     => "1",
  august    => "2",
  september => "2"
]

Sorry for bad english and thanks!

Answer 1

have you tried using MONTHNAME() function?

instead of:

$qb->select(['MONTH(p.createdAt) as month', 'count(ccp) as insertions'])

try:

$qb->select(['MONTHNAME(p.createdAt) as month', 'count(ccp) as insertions'])