Avez-vous une question? (216) 97 656 803 hajjriadh@gmail.com
NestJS

Requêtes avancées avec le QueryBuilder

Cette leçon présente le QueryBuilder de TypeORM, pour les requêtes que find() ne permet pas d'écrire. Huit requêtes exécutées sur des données de stagiaires, groupes et notes couvrent : conditions et p...

0 chapitres 0 QCM Accès gratuit
Requêtes avancées avec le QueryBuilder

À propos de ce cours

Cette leçon présente le QueryBuilder de TypeORM, pour les requêtes que find() ne permet pas d'écrire. Huit requêtes exécutées sur des données de stagiaires, groupes et notes couvrent : conditions et paramètres nommés avec getSql(), jointures et conditions groupées avec Brackets, agrégations AVG et COUNT avec GROUP BY et HAVING, statistiques par groupe, sous-requête, page et total avec getManyAndCount, mise à jour en masse, et une injection SQL réelle quand on concatène une valeur. La différence entre getMany et getRawMany est expliquée. Activités corrigées.

Objectifs de la leçon

  • Construire une requête avec createQueryBuilder.
  • Utiliser jointures, conditions groupées et paramètres nommés.
  • Écrire des agrégations (COUNT, AVG, GROUP BY, HAVING) et des sous-requêtes.
  • Choisir entre getMany et getRawMany.
  • Ne jamais concaténer de valeurs dans le QueryBuilder.
Quand utiliser le QueryBuilder ?

find() couvre la plupart des lectures. Le QueryBuilder devient nécessaire pour les calculs (moyennes, comptages par groupe), les conditions complexes (OU imbriqués), les sous-requêtes, ou pour contrôler précisément les jointures. Il construit la requête méthode par méthode, avec une syntaxe proche du SQL.

Le QueryBuilder de TypeORM

Chaque méthode ajoute une clause ; une méthode get… exécute la requête.

Les données

On reprend les entités de la leçon 11.5 (groupes, stagiaires, notes) avec ces données :

  const groupes = source.getRepository(Groupe);
  const stagiaires = source.getRepository(Stagiaire);
  const [g1, g2] = await groupes.save([
    { code: 'DEV101', intitule: 'Développement web 1' }, { code: 'DEV102', intitule: 'Développement web 2' },
    { code: 'RES201', intitule: 'Réseaux' },
  ]);
  const donnees: [string, Groupe | null, [string, number][]][] = [
    ['Ben Ali', g1, [['SQL', 15], ['JS', 12.5], ['PHP', 14]]],
    ['Trabelsi', g1, [['SQL', 9], ['JS', 7.5]]],
    ['Gharbi', g2, [['SQL', 17], ['JS', 16], ['PHP', 18]]],
    ['Mansouri', g2, [['SQL', 11]]],
    ['Jlassi', null, []],
  ];
  for (const [nom, groupe, notes] of donnees) {
    await stagiaires.save({ nom, groupe, notes: notes.map(([matiere, valeur]) => ({ matiere, valeur })) });
  }
Huit requêtes
  // 1. Sélection simple avec paramètres nommés
  const qb1 = stagiaires
    .createQueryBuilder('s')
    .where('s.nom LIKE :debut', { debut: 'G%' })
    .orWhere('s.groupeId IS NULL')
    .orderBy('s.nom', 'ASC');
  console.log('1. SQL :', qb1.getSql());
  console.log('   résultat :', (await qb1.getMany()).map((s) => s.nom));

  // 2. Jointure + conditions groupées (Brackets)
  const liste = await stagiaires
    .createQueryBuilder('s')
    .leftJoinAndSelect('s.groupe', 'g')
    .where('g.code IN (:...codes)', { codes: ['DEV101', 'DEV102'] })
    .andWhere(new Brackets((qb) => qb.where('s.nom LIKE :a', { a: 'B%' }).orWhere('s.nom LIKE :b', { b: 'M%' })))
    .getMany();
  console.log('2. jointure + Brackets :', liste.map((s) => `${s.nom} (${s.groupe?.code})`));

  // 3. Agrégation : moyenne et nombre de notes par stagiaire, avec HAVING
  const moyennes = await stagiaires
    .createQueryBuilder('s')
    .innerJoin('s.notes', 'n')
    .select('s.nom', 'nom')
    .addSelect('COUNT(n.id)', 'nbNotes')
    .addSelect('ROUND(AVG(n.valeur), 2)', 'moyenne')
    .groupBy('s.id')
    .having('AVG(n.valeur) >= :seuil', { seuil: 10 })
    .orderBy('moyenne', 'DESC')
    .getRawMany();
  console.log('3. moyennes ≥ 10 :', moyennes);

  // 4. Agrégation par groupe (getRawMany renvoie des lignes « brutes »)
  const parGroupe = await source
    .createQueryBuilder()
    .select('g.code', 'groupe')
    .addSelect('COUNT(DISTINCT s.id)', 'stagiaires')
    .addSelect('ROUND(AVG(n.valeur), 2)', 'moyenne')
    .from(Groupe, 'g')
    .leftJoin('g.stagiaires', 's')
    .leftJoin('s.notes', 'n')
    .groupBy('g.id')
    .orderBy('g.code')
    .getRawMany();
  console.log('4. par groupe :', parGroupe);

  // 5. Sous-requête : stagiaires au-dessus de la moyenne générale
  const moyenneGenerale = source.createQueryBuilder().subQuery().select('AVG(n2.valeur)').from(Note, 'n2').getQuery();
  const auDessus = await stagiaires
    .createQueryBuilder('s')
    .innerJoin('s.notes', 'n')
    .select('s.nom', 'nom')
    .addSelect('AVG(n.valeur)', 'moy')
    .groupBy('s.id')
    .having(`AVG(n.valeur) > ${moyenneGenerale}`)       // moyenneGenerale = '(SELECT AVG(n2.valeur) FROM notes n2)'
    .getRawMany();
  console.log('5. au-dessus de la moyenne générale :', auDessus.map((r) => r.nom));

  // 6. getManyAndCount : une page + le total
  const [page, total] = await stagiaires.createQueryBuilder('s').orderBy('s.nom').skip(0).take(2).getManyAndCount();
  console.log('6. page 1 (2 par page) :', page.map((s) => s.nom), '| total :', total);

  // 7. Mise à jour en masse
  const maj = await source
    .createQueryBuilder()
    .update(Note)
    .set({ valeur: () => 'LEAST(valeur + 1, 20)' })
    .where('matiere = :matiere', { matiere: 'SQL' })
    .execute();
  console.log('7. bonus SQL : lignes modifiées =', maj.affected);

  // 8. Le piège : construire la condition par concaténation
  const piege = "x' OR '1'='1";
  const dangereux = await stagiaires.createQueryBuilder('s').where(`s.nom = '${piege}'`).getCount();
  const sur = await stagiaires.createQueryBuilder('s').where('s.nom = :nom', { nom: piege }).getCount();
  console.log(`8. concaténation : ${dangereux} lignes ✗ | paramètre : ${sur} ligne ✓`);

Résultat réellement obtenu :

1. SQL : SELECT `s`.`id` AS `s_id`, `s`.`nom` AS `s_nom`, `s`.`groupe_id` AS `s_groupe_id` FROM `stagiaires` `s` WHERE `s`.`nom` LIKE ? OR `s`.`groupe_id` IS NULL ORDER BY `s`.`nom` ASC
   résultat : [ 'Gharbi', 'Jlassi' ]
2. jointure + Brackets : [ 'Ben Ali (DEV101)', 'Mansouri (DEV102)' ]
3. moyennes ≥ 10 : [
  { nom: 'Gharbi', nbNotes: '3', moyenne: '17.00' },
  { nom: 'Ben Ali', nbNotes: '3', moyenne: '13.83' },
  { nom: 'Mansouri', nbNotes: '1', moyenne: '11.00' }
]
4. par groupe : [
  { groupe: 'DEV101', stagiaires: '2', moyenne: '11.60' },
  { groupe: 'DEV102', stagiaires: '2', moyenne: '15.50' },
  { groupe: 'RES201', stagiaires: '0', moyenne: null }
]
5. au-dessus de la moyenne générale : [ 'Ben Ali', 'Gharbi' ]
6. page 1 (2 par page) : [ 'Ben Ali', 'Gharbi' ] | total : 5
  SQL> UPDATE `notes` SET `valeur` = LEAST(`valeur` + 1, 20) WHERE `matiere` = ? -- ["SQL"]
7. bonus SQL : lignes modifiées = 4
8. concaténation : 5 lignes ✗ | paramètre : 0 ligne ✓
Explications
  1. createQueryBuilder('s'): s est l'alias de l'entité, utilisé ensuite dans toutes les clauses (s.nom). On utilise les noms des propriétés (s.groupeId), TypeORM les traduit en colonnes (groupe_id). getSql() affiche le SQL sans l'exécuter : très utile pour comprendre ou déboguer.
  2. :...codes (avec trois points) développe un tableau pour IN. Brackets place des parenthèses : … AND (nom LIKE 'B%' OR nom LIKE 'M%'). Sans elles, la priorité de AND sur OR donnerait un autre résultat.
  3. select / addSelect avec un alias, groupBy, having: on obtient des lignes de calcul, pas des entités.
  4. Pour partir d'une autre table, source.createQueryBuilder().from(Groupe, 'g'). Les LEFT JOIN conservent RES201, qui n'a aucun stagiaire (moyenne null).
  5. subQuery() produit le texte d'une sous-requête, intégré dans having.
  6. skip / take + getManyAndCount(): une page et le nombre total (leçon 11.7).
  7. update().set().where().execute(): une seule requête pour toutes les notes de SQL ; () => 'LEAST(valeur + 1, 20)' insère une expression SQL au lieu d'une valeur.
  8. La concaténation de ' OR '1'='1 a renvoyé les 5 stagiaires : injection SQL. Avec un paramètre, 0 ligne.
getMany ou getRawMany ?
getMany() / getOne()getRawMany() / getRawOne()
Résultatdes entités (avec leurs relations jointes par …AndSelect)des objets simples avec les alias choisis
Calculs (AVG, COUNT)impossibles à récupéreroui
Typesconvertis (transformateurs appliqués)bruts : COUNT et AVG arrivent en chaînes ('3', '13.83')

Pensez à convertir les valeurs de getRawMany() avec Number() avant de les renvoyer (TP 7, statistiques).

leftJoin joint la table pour pouvoir l'utiliser dans les conditions ; leftJoinAndSelect la joint et charge ses colonnes dans l'entité. Même différence pour innerJoin / innerJoinAndSelect. Et pour du SQL vraiment particulier, source.query('SELECT … WHERE x = ?', [valeur]) reste possible, toujours avec des paramètres.

Activités

Activité 1 Bulletin d'un groupe

Écrivez une requête qui renvoie, pour un groupe donné, chaque stagiaire avec sa moyenne (2 décimales), son nombre de notes et sa meilleure note, triés par moyenne décroissante. Les stagiaires sans note doivent apparaître avec une moyenne null.

Voir le corrigé
const lignes = await this.stagiaires
  .createQueryBuilder('s')
  .leftJoin('s.notes', 'n')                     // LEFT : garde les stagiaires sans note
  .select('s.id', 'id')
  .addSelect('s.nom', 'nom')
  .addSelect('ROUND(AVG(n.valeur), 2)', 'moyenne')
  .addSelect('COUNT(n.id)', 'nbNotes')
  .addSelect('MAX(n.valeur)', 'meilleure')
  .where('s.groupeId = :groupeId', { groupeId })
  .groupBy('s.id')
  .orderBy('moyenne', 'DESC')
  .getRawMany();

return lignes.map((l) => ({ ...l, moyenne: l.moyenne === null ? null : Number(l.moyenne), nbNotes: Number(l.nbNotes) }));

Activité 2 Statistiques par matière

Pour chaque matière : nombre de notes, moyenne, nombre de notes inférieures à 10 (indice : SUM(CASE WHEN … THEN 1 ELSE 0 END)), seulement pour les matières qui ont au moins 3 notes.

Voir le corrigé
source.createQueryBuilder()
  .select('n.matiere', 'matiere')
  .addSelect('COUNT(*)', 'total')
  .addSelect('ROUND(AVG(n.valeur), 2)', 'moyenne')
  .addSelect('SUM(CASE WHEN n.valeur < :seuil THEN 1 ELSE 0 END)', 'sousLaMoyenne')
  .from(Note, 'n')
  .groupBy('n.matiere')
  .having('COUNT(*) >= :min')
  .setParameters({ seuil: 10, min: 3 })
  .getRawMany();

Activité 3 Brackets

Écrivez la condition : stagiaires du groupe DEV101 et (nom commençant par B ou ayant au moins une note supérieure à 16). Que renverrait la même requête sans Brackets?

Voir le corrigé
qb.where('g.code = :code', { code: 'DEV101' })
  .andWhere(new Brackets((b) =>
    b.where('s.nom LIKE :b', { b: 'B%' })
     .orWhere('EXISTS (SELECT 1 FROM notes n WHERE n.stagiaire_id = s.id AND n.valeur > :v)', { v: 16 }),
  ));

Sans parenthèses : code = 'DEV101' AND nom LIKE 'B%' OR EXISTS(…), interprété comme (code = 'DEV101' AND nom LIKE 'B%') OR EXISTS(…): Gharbi (DEV102) serait inclus à tort.

Activité 4 Corriger une faille

Corrigez ce code de tri : qb.orderBy(`s.${req.query.tri}`). Pourquoi un paramètre :tri ne fonctionnerait-il pas ici ?

Voir le corrigé

Un paramètre remplace une valeur, pas un nom de colonne : ORDER BY ? trierait par une constante. Il faut une liste blanche: const champs = { nom: 's.nom', date: 's.creeLe' }; const colonne = champs[tri] ?? 's.nom'; qb.orderBy(colonne);, ou un DTO avec @IsIn([...]) (leçon 11.7). Le texte du client n'entre jamais dans le SQL.

À retenir

  • createQueryBuilder('alias') : select, join, where, groupBy, having, orderBy, skip/take, puis get….
  • Paramètres nommés (:nom, :...liste) ; Brackets pour les parenthèses ; getSql() pour voir le SQL.
  • getMany → entités ; getRawMany → lignes de calcul, valeurs en chaînes à convertir.
  • update()/delete() du QueryBuilder : opérations en masse en une requête.
  • Jamais de valeur ni de nom de colonne du client concaténé : paramètres et listes blanches.

Cet article vous a-t-il aidé ?