À propos de ce cours
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.
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
createQueryBuilder('s'):sest 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.:...codes(avec trois points) développe un tableau pourIN.Bracketsplace des parenthèses :… AND (nom LIKE 'B%' OR nom LIKE 'M%'). Sans elles, la priorité deANDsurORdonnerait un autre résultat.select/addSelectavec un alias,groupBy,having: on obtient des lignes de calcul, pas des entités.- Pour partir d'une autre table,
source.createQueryBuilder().from(Groupe, 'g'). LesLEFT JOINconservent RES201, qui n'a aucun stagiaire (moyennenull). subQuery()produit le texte d'une sous-requête, intégré danshaving.skip/take+getManyAndCount(): une page et le nombre total (leçon 11.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.- La concaténation de
' OR '1'='1a renvoyé les 5 stagiaires : injection SQL. Avec un paramètre, 0 ligne.
getMany ou getRawMany ?
getMany() / getOne() | getRawMany() / getRawOne() | |
|---|---|---|
| Résultat | des entités (avec leurs relations jointes par …AndSelect) | des objets simples avec les alias choisis |
Calculs (AVG, COUNT) | impossibles à récupérer | oui |
| Types | convertis (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é ?
Merci pour votre avis sur cet article 🙏
Merci, votre message nous a bien été transmis 🙏
Cette leçon vous a été utile ? Partagez-la !