Pourquoi ma requête LEFT JOIN ne renvoie-t-elle pas les organisations avec un nombre nul ?

PostgreSQL LEFT JOIN : résoudre les problèmes d'organisation à nombre nul

Un défi courant lors de l'utilisation de LEFT JOIN dans PostgreSQL consiste à récupérer le nombre d'enregistrements associés. Si le décompte est nul pour une organisation particulière, cette organisation peut être omise des résultats. Cet article répond à ce problème.

La requête suivante tente de compter les articles utilisés par organisation :

<code class="language-sql">SELECT o.name as organisation_name,
         coalesce(COUNT(exam_items.id)) as total_used
  FROM organisations o
  LEFT JOIN exam_items e on o.id = e.organisation_id
  WHERE e.item_template_id = #{sanitize(item_template_id)}
  and e.used = true
  GROUP BY o.name
  ORDER BY o.name</code>

Le défaut de cette requête réside dans la clause WHERE. Conditions placées dans le filtre de clause WHERE après la jointure, transformant efficacement le LEFT JOIN en INNER JOIN lorsqu'un enregistrement correspondant n'est pas trouvé dans exam_items.

Voici des versions améliorées :

Solution 1 : Corriger la condition JOIN

La clause WHERE filtre les organisations avec un décompte nul. Déplacer les conditions item_template_id et used dans la clause ON du LEFT JOIN résout ceci :

<code class="language-sql">SELECT o.name AS organisation_name, COUNT(e.id) AS total_used
FROM organisations o
LEFT JOIN exam_items e ON e.organisation_id = o.id AND e.item_template_id = #{sanitize(item_template_id)} AND e.used
GROUP BY o.name
ORDER BY o.name;</code>

Cette requête révisée garantit que toutes les organisations sont incluses, même celles qui n'ont aucune entrée correspondante dans exam_items.

Solution 2 : Pré-agrégation pour plus d'efficacité

Pour les ensembles de données plus volumineux, la pré-agrégation exam_items peut améliorer considérablement les performances :

<code class="language-sql">SELECT o.id, o.name AS organisation_name, COALESCE(e.total_used, 0) AS total_used
FROM organisations o
LEFT JOIN (
   SELECT organisation_id AS id, COUNT(*) AS total_used
   FROM exam_items
   WHERE item_template_id = #{sanitize(item_template_id)} AND used = TRUE
   GROUP BY 1
) e ON o.id = e.id
ORDER BY o.name, o.id;</code>

Cette approche regroupe d'abord les décomptes pour chaque organisation, puis effectue le LEFT JOIN, ce qui permet d'obtenir une requête plus efficace. Notez l'utilisation de COALESCE pour gérer les cas où total_used est NULL.

En mettant en œuvre l'une ou l'autre de ces solutions, vous récupérerez avec précision le nombre d'articles utilisés pour toutes les organisations, y compris celles avec un nombre nul. Choisissez la solution la mieux adaptée à la taille de vos données et à vos exigences de performances.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!