Repository et requêtes DQL

Repository et requêtes DQL
Symfony

1 May 2021

Doctrine est un ORM très puissant, mais pas toujours bien mis en avant quand il s'agit des requêtes DQL (Doctrine Query Language). Nous allons justement voir dans cet article comment créer des requêtes SQL avec Doctrine.

Il y a une chose importante à savoir avec les requêtes DQL : Doctrine ne possède pas toutes les fonctions que vous pouvez avoir avec des requêtes SQL. Il existe des bundles pour pallier ces problèmes, mais pas pour tous les cas. Rassurez-vous, il est toujours possible de créer des requêtes SQL habituelles avec Doctrine. ?

Les requêtes personnalisées s'écriront dans un repository, mais pas n'importe lequel : on choisit le repository de l'entité avec laquelle on souhaite travailler. En gros, je ne vais pas faire de requêtes dans le repository "VillesRepository.php" si je compte effectuer une recherche sur l'entité "Agences.php". Alors oui, vous pouvez, mais question organisation, ce n’est pas terrible. ?

Voyons comment faire une requête avec Doctrine. Bien entendu, s'il s'agit d'une sélection avec un simple ID, ne faites pas votre propre requête, cela n'a aucun intérêt puisque le repository vous propose déjà quatre méthodes de sélection très pratiques.

Pour mon cas, je veux pouvoir sélectionner toutes les agences créées entre deux dates. En SQL, cela donnerait :

SELECT * FROM agences WHERE created_at BETWEEN value1 AND value2;

En DQL :

public function findAgenciesByDates($dateOne, $dateTwo)
{
   return $this->createQueryBuilder('a')
      ->andWhere('a.created_at BETWEEN :dateOne AND :dateTwo')
      ->setParameters([
         'dateOne' => $dateOne,
         'dateTwo' => $dateTwo
      ])
      ->getQuery()
      ->getResult()
   ;
}

Détaillons un peu le tout.

$this->createQueryBuilder('a')

createQueryBuilder() va générer un alias de la table "agences". Généralement, la première lettre de la table en question est sélectionnée.

->andWhere('a.created_at BETWEEN :dateOne AND :dateTwo')

andWhere() est d'après moi assez facile à comprendre. Ici, vous insérez votre condition comme vous le feriez en SQL. En lieu et place des valeurs, j'ai indiqué deux variables internes :dateOne et :dateTwo. J'ai ajouté le préfixe a à created_at. Ce préfixe est l'alias de ma table. En DQL, vous devez toujours ajouter l'alias en préfixe de vos noms de colonnes.

->setParameters([
  'dateOne' => $dateOne,
  'dateTwo' => $dateTwo
])

Vous l'aurez deviné, il s'agit de passer des valeurs aux variables internes du andWhere(). Il existe deux méthodes pour mettre en place les paramètres :

setParameters(['key' => 'value']);
// OR
setParameter('key', 'value');

La première est faite avec un tableau et est utile si vous avez plusieurs valeurs à soumettre et la seconde est appropriée si vous avez une seule valeur à passer. Faites bien attention, la première méthode comporte un "s" dans son nom, mais pas la suivante.

->getQuery()

Exécute la requête.

->getResult()

Retourne un tableau de résultats ou null s'il n'y a aucune donnée.

Notre code actuel nous permet de récupérer des agences selon des paramètres définis, mais récupère lui-même la totalité des informations trouvées. Si je veux pouvoir récupérer que l'ID et le nom, il va falloir modifier la requête un petit peu. ?

public function findAgenciesByDates($dateOne, $dateTwo)
{
   return $this->createQueryBuilder('a')
      ->select('a.id, a.name')
      ->andWhere('a.created_at BETWEEN :dateOne AND :dateTwo')
      ->setParameters([
         'dateOne' => $dateOne,
         'dateTwo' => $dateTwo
      ])
      ->getQuery()
      ->getResult()
   ;
}

J'ai gardé mon code d'origine et ajouté après mon createQueryBuilder(), la méthode select() dans laquelle je demande à récupérer simplement les informations des colonnes ID et name.

Rappel : J'ai préfixé les noms avec l'alias de ma table. En DQL, vous devez toujours ajouter en préfixe l'alias de la table, sinon... erreur !

Il existe d'autres méthodes pour alimenter votre code comme orderBy, groupBy...
Je vous conseille de regarder cette partie de la documentation de Doctrine : https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/query-builder.html

Sélectionner un seul résultat

Comme je l'ai précisé plus haut, mon code récupère soit zéro, soit plusieurs résultats. Si je veux qu'un seul résultat me soit renvoyé, il me suffit de modifier le getResult() par :

->getOneOrNullResult()

La requête vous retournera soit un résultat, soit null. En même temps, je crois que le nom de la méthode parle d'elle-même. ?

Jointure

Les jointures en DQL sont possibles aussi ! Je vais reprendre ma requête du dessus et ajouter une jointure avec mon entité "Villes.php".

public function findAgenciesByDates($dateOne, $dateTwo)
{
   return $this->createQueryBuilder('a')
      ->select('a.id, a.name, v.name')
      ->join('a.ville', 'v')
      ->andWhere('a.created_at BETWEEN :dateOne AND :dateTwo')
      ->andWhere('v.name = "Troyes"')
      ->setParameters([
         'dateOne' => $dateOne,
         'dateTwo' => $dateTwo
      ])
      ->getQuery()
      ->getResult()
   ;
}

En SQL, elle ressemblerait à :

SELECT a.id, a.name, v.name 
FROM agences a
INNER JOIN villes v
WHERE a.created_at BETWEEN '2020-08-01' AND '2020-08-08' AND v.name = 'Troyes'

Ma nouvelle ligne récupère la propriété "ville", présente dans mon entité "Agences.php", qui est ma clé étrangère en base de données. C'est ma propriété de relation. Enfin, je lui donne un alias pour pouvoir l'utiliser dans le reste de ma requête.

->join('a.ville', 'v')

Repository dans le contrôleur

OK, c'est bien joli tout ça, mais comment j'utilise cette méthode dans mon contrôleur ?
Il n'y a rien de bien différent par rapport aux méthodes find(), findAll(), findBy() et findOneBy() !
Je vous montre, car je sens que vous aimez les exemples :

/**
 * @Route("/home", name="home")
 */
public function index()
{
  $agences = $this->getDoctrine()->getRepository(Agences::class)->findAgenciesByDates(
    new DateTime('2020-08-01'),
    new DateTime('2020-08-10')
  );

  return $this->render('home/index.html.twig');
}

SQL native

Qu'en est-il des requêtes SQL habituelles ? Rassurez-vous, vous pouvez toujours faire des requêtes SQL "à l'ancienne" dans le repository. ?

Je vais reprendre ma requête DQL du dessus et la passer en SQL :

public function findAgenciesByIDs($dateOne, $dateTwo)
{
   $conn = $this->getEntityManager()->getConnection();
   $stmt = $conn->prepare("
      SELECT a.id, a.name, v.name 
      FROM agences a
      INNER JOIN villes v
      WHERE a.created_at BETWEEN :dateOne AND :dateTwo AND v.name = 'Troyes'
   ");

   $stmt->execute(['dateOne' => $dateOne, 'dateTwo' => $dateTwo]);
   return $stmt->fetchAll();
}

Vous reconnaîtrez le fetchAll() qui retourne tous les résultats. Donc, si vous voulez récupérer un seul résultat, utilisez fetch(). ?