Error during execution of a Doctrine DQL query converted from SQL

Question

I have a problem rewriting an SQL query that works correctly with MySql in Doctrine Query Language (DQL). I have two tables with the following structures:

--
-- Structure de la table `ame`
--

CREATE TABLE `ame` (
  `id` int NOT NULL,
  `profile_id` int DEFAULT NULL,
  `date_ajout` date NOT NULL,
  `type_ame_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table 2:

--
-- Structure de la table `type_ame`
--

CREATE TABLE `type_ame` (
  `id` int NOT NULL,
  `nom` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

my problem is that I managed to write my sql request which counts me and returns the list of type_ame grouping them by date. Here is the sql request that works but I can't write it in DQL

SELECT a.date_ajout, COUNT(a.type_ame_id), ta.nom
FROM ame a
LEFT JOIN type_ame ta on a.type_ame_id = ta.id
WHERE a.profile_id = 2
GROUP BY a.date_ajout, a.type_ame_id
ORDER BY date_ajout ASC

the Next SQL code returns the following result

This is my repository function but

public function  findStatistique($user)
{
    $entityManager = $this->getEntityManager();

      $query = $entityManager->createQuery(
        'SELECT a.dateAjout, COUNT(a.typeAme), ta.nom
            FROM App\Entity\Ame a
            LEFT JOIN App\Entity\TypeAme ta
            WHERE a.profile = 2
            GROUP BY a.dateAjout, a.typeAme
            ORDER BY a.dateAjout ASC'
    );
    return $query->getResult();

erreur-symfony-5-sql-dql

but I can't convert it to doctine DQL language

Answer 1

I found the solution:

public function  findStatistique($id)
{
    $entityManager = $this->getEntityManager();

    $query = $entityManager->createQueryBuilder('a');
                $query->select('a.dateAjout', 'count(a.typeAme)', 'ta.nom');
                $query->from('App:Ame', 'a');
                $query->leftJoin('a.typeAme', 'ta');
                $query->groupBy('a.dateAjout', 'a.typeAme', 'a.profile');
                $query->having('a.profile = :id');
                $query->orderBy('a.dateAjout', 'ASC');
                $query->setParameter('id', $id);

    return $query->getQuery()->getResult();

}