Consulta con LEFT JOIN que no devuelve filas para un recuento de 0
Estoy intentando que lo siguiente devuelva un recuento para cada organización que utiliza una combinación izquierda en PostgreSQL, pero no puedo entender por qué no funciona:
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
El uso coalesceno parece funcionar. ¡Estoy al final de mi ingenio! ¡Cualquier ayuda sería ciertamente apreciada!
Para aclarar lo que no funciona, por el momento la consulta solo devuelve valores para organizaciones que tienen un recuento mayor que 0. Me gustaría que devuelva una línea para cada organización, independientemente del recuento.
Definiciones de la tabla:
TABLE exam_items
id serial NOT NULL
exam_id integer
item_version_id integer
used boolean DEFAULT false
question_identifier character varying(255)
organisation_id integer
created_at timestamp without time zone NOT NULL
updated_at timestamp without time zone NOT NULL
item_template_id integer
stem_id integer
CONSTRAINT exam_items_pkey PRIMARY KEY (id)
TABLE organisations
id serial NOT NULL
slug character varying(255)
name character varying(255)
code character varying(255)
address text
organisation_type integer
created_at timestamp without time zone NOT NULL
updated_at timestamp without time zone NOT NULL
super boolean DEFAULT false
CONSTRAINT organisations_pkey PRIMARY KEY (id)
Arregla elLEFT JOIN
Esto debería funcionar:
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;
Tenías una LEFT [OUTER] JOIN, pero las condiciones posteriores WHEREhicieron que actuara como una llanura [INNER] JOIN.
Mueva las condiciones a la JOINcláusula para que funcione según lo previsto. De esta manera, sólo las filas que cumplen todas estas condiciones se unen en primer lugar (o las columnas de la tabla derecha se rellenan con NULL). Como lo hizo, las filas unidas se prueban para detectar condiciones adicionales prácticamente después y LEFT JOINse eliminan si no pasan, al igual que con un simple JOIN.
count()Para empezar, nunca devuelve NULL. Es una excepción entre las funciones agregadas a este respecto. Por lo tanto, nunca tiene sentido, incluso con parámetros adicionales. El manual:COALESCE(COUNT(col))
Cabe señalar que, excepto
count, estas funciones devuelven un valor nulo cuando no se selecciona ninguna fila.
El énfasis en negrita es mío. Ver:
- Cuente el número de atributos que son NULL para una fila
count()debe estar en una columna definida NOT NULL(como e.id), o donde la condición de unión garantice NOT NULL( e.organisation_id, e.item_template_ido e.used) en el ejemplo.
Dado usedque es tipo boolean, la expresión e.used = truees ruido que se reduce a solo e.used.
Dado que o.nameno está definido UNIQUE NOT NULL, es posible que desee GROUP BY o.idhacerlo ( idsiendo el PK), a menos que tenga la intención de plegar filas con el mismo nombre (incluido NULL).
Agrega primero, únete después
Si se cuentan la mayoría o todas las filas exam_itemsen el proceso, esta consulta equivalente suele ser considerablemente más rápida/más barata:
SELECT o.id, o.name AS organisation_name, e.total_used
FROM organisations o
LEFT JOIN (
SELECT organisation_id AS id -- alias to simplify join syntax
, count(*) AS total_used -- count(*) = fastest to count all
FROM exam_items
WHERE item_template_id = #{sanitize(item_template_id)}
AND used
GROUP BY 1
) e USING (id)
ORDER BY o.name, o.id;
(Esto supone que no desea doblar filas con el mismo nombre como se mencionó anteriormente; el caso típico).
Ahora podemos usar el más rápido/simple count(*)en la subconsulta, y no necesitamos GROUP BYen el exterior SELECT.
Ver:
- Múltiples llamadas a array_agg() en una sola consulta
Para hacerlo claro,
la línea importante es GROUP BY MAIN_TABLEque manejará el valor NULL deSOME_TABLE
SELECT COUNT(ST.ID)
FROM MAIN_TABLE MT
LEFT JOIN SOME_TABLE ST ON MT.ID = ST.MT_ID
GROUP BY MT.ID -- this line is a must
