SaleService.php

<?php

namespace App\Service;

use App\Entity\Sale;
use Doctrine\ORM\EntityManagerInterface;
use App\Dto\MonthlyAveragePriceEvolutionDto;

class SaleService
{
    private EntityManagerInterface $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function getMonthlyAveragePriceEvolution(): array
    {
        $queryBuilder = $this->entityManager->createQueryBuilder();

        $result = $queryBuilder
            ->select('MONTH(s.date) as month', 'YEAR(s.date) as year', 'AVG(CASE WHEN s.surface <> 0 THEN s.amount / s.surface ELSE 0 END) as average_price')
            ->from(Sale::class, 's')
            ->groupBy('year, month')
            ->orderBy('year, month')
            ->getQuery()
            ->getResult();

        $monthlyAveragePriceEvolutions = [];
        foreach ($result as $row) {
            $monthlyAveragePriceEvolutions[] = new MonthlyAveragePriceEvolutionDto(
                (int)$row['month'],
                (int)$row['year'],
                (float)$row['average_price']
            );
        }

        return $monthlyAveragePriceEvolutions;
    }
}