Mieux utiliser SQL et Doctrine

1. Problèmatique
2. Rappel SQL
3. Doctrine

Problèmatique

• ORM
• Requêtes complexes
• Déporter un maximum de calcul coté SQL
• Performances

Rappels SQL

Qu'est ce qui est lent / couteux ?

• Selectionner trop de données
• Faire trop de calcul
• Clauses sur des colonnes non indexées

Solutions

• Comprendre comment fonctionne SQL
• Optimiser ses requêtes
• Mieux filtrer
• Utiliser les index
• Toujours penser aux nombres de lignes

Ordre d'éxécution

SELECT ... FROM ... LEFT JOIN ... WHERE ...
GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

1. FROM / JOIN

• Bien choisir entre INNER JOIN ou LEFT JOIN
• (RIGHT JOIN / FULL JOIN)
• Utiliser au maximum le ON

Exemple

SELECT *
FROM article a INNER JOIN user u ON a.user_id = u.id
WHERE a.active = TRUE

SELECT *
FROM article a INNER JOIN user u ON (a.user_id = u.id AND a.active = TRUE)

2. WHERE

• Pas d'accès aux colonnes créées dans le SELECT

Exemple

SELECT username AS login
FROM user
WHERE login = 'foobar'

SELECT u.username AS login
FROM user u
WHERE u.username = 'foobar'

3. GROUP BY

• Groupe plusieurs lignes
• A utiliser avec une foncton d’agrégation (SUM, COUNT, MAX, MIN, AVG)
• Toutes les colonnes non agréger doivent être grouper

Exemple

SELECT name, amount FROM order

Exemple

SELECT name, SUM(amount) FROM orders GROUP BY name

4. HAVING

• Similaire au WHERE
• Supporte les agrégats
• Toujours pas d'accès au colonne créées dans le SELECT
• Généralement utilisé avec GROUP BY

Exemple

SELECT name, SUM(amount) AS total
FROM orders
GROUP BY name
HAVING total > 200

SELECT name, SUM(amount) AS total
FROM orders
GROUP BY name
HAVING SUM(amount) > 200

5. SELECT

• Sélectionne les colonnes

6. ORDER BY

• Accès aux colonnes créées dans le SELECT
• Peu être multi-colonne => ordre important

Exemple 1

SELECT name, SUM(amount) AS total GROUP BY name ORDER BY total ASC

Exemple 2

SELECT name, amount ORDER BY amount ASC, name DESC

Exemple 3

SELECT name, amount ORDER BY name, amount ASC

6. LIMIT

• Exécuter en tout dernier
• Les calculs et clauses sont exécutés sur toute la table avant le LIMIT
• Le LIMIT s'applique sur les lignes retournées (attention au jointure N-N)

Exemple

SELECT name, SUM(amount) AS total 
FROM orders 
GROUP BY name 
ORDER BY total DESC 
LIMIT 0, 2

Le COUNT, GROUP BY et le ORDER BY s'applique sur toute la table

ANY (SOME)

• ANY (SOME) : comparaison avec au moins un résultat d'une sous requête
• WHERE id = ANY(...) équavalent de WHERE id IN (...)

SELECT * FROM orders WHERE id = ANY (SELECT ...)

ALL

• ALL : comparaison avec l'ensemble des resultats d'une sous requête

SELECT * FROM orders WHERE amount > ALL (SELECT ...)

EXISTS

• EXISTS : vérifie qu'une sous requête retourne au moins un résultat

SELECT * FROM orders WHERE EXISTS (SELECT ...)

Doctrine

Jointures

$this->createQueryBuilder('article')->leftJoin('category')

$this->createQueryBuilder('article')
     ->select('article, category')
     ->leftJoin('category')

Ne retourner que les données utiles

• Fetch mode (EAGER, LASY)
• Hydration mode (HYDRATE_OBJECT, HYDRATE_ARRAY, ...)
• Partial objects (partial article.{id, title})

Hydration

• Utiliser des HYDRATE_ARRAY ou des custom hydrator pour les listes
• Utiliser des objects de vue

Sous requêtes

$this->createQueryBuilder('c')
     ->select('c.id, COUNT(a.id)')
     ->leftJoin('c.articles', 'a')
     ->groupBy('a.id')

$this->createQueryBuilder('c')
     ->select([
        'c.id', 
        '(SELECT COUNT(a.id) FROM Model:Article a WHERE a.category = c)'
     ])

Exemple :(

$this
     ->createQueryBuilder('article')
     ->select('article, category, tag')
     ->leftJoin('article.category', 'category')
     ->leftJoin('article.tags', 'tag')

$articles->getTags()->count()

Exemple :)

$this
     ->createQueryBuilder('article')
     ->select([
        'article.id AS articleId', 
        'article.name AS articleName', 
        'category.id AS categoryId', 
        'category.name AS categoryName'
        '(SELECT COUNT(t.id) FROM Model:Tag t 
        INNER JOIN tag.articles a WITH a = article)'
     ])
     ->leftJoin('article.category', 'category')

KNP Paginator

Peut être très lent lorsque la requête est complexe ou qu'il y a trop de ligne

1. Compte le nombre de resultat dans la request à paginer
2. Sélectionne les PK de la page courante
3. Sélectionne les lignes correspondants à ces PK

Solution

Evènement 'knp_paginator.subscriber' et pagination manuel

1. Ecrire une requête de comptage minimaliste
2. Sélectionner les PK de la page courante
3. Sélectionner les données, pas de filtrage autre que sur les PK

CASCADE

Utiliser les CASCADE du SGBD au lieu de ceux de Doctrine

• + Plus rapide, pas d'appel à Doctrine
• - Pas d'event

Exemple

@OneToMany(targetEntity="Comment", mappedBy="author", cascade={"remove"})

@JoinColumn(onDelete="CASCADE")