Query con LEFT JOIN che non restituisce righe per il conteggio di 0

Sto cercando di ottenere quanto segue per restituire un conteggio per ogni organizzazione utilizzando un join di sinistra in PostgreSQL, ma non riesco a capire perché non funzioni:

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 

L’uso della coalesce non sembra funzionare. Sono alla fine del mio spirito! Qualsiasi aiuto sarebbe sicuramente apprezzato!

Per chiarire cosa non funziona, al momento la query restituisce solo i valori per le organizzazioni che hanno un conteggio maggiore di 0. Mi piacerebbe che restituisse una riga per ogni organizzazione, indipendentemente dal conteggio.

Definizioni di tabella:

 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) 

Questo dovrebbe funzionare:

 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 = true GROUP BY o.name ORDER BY o.name; 
  • Hai avuto un LEFT [OUTER] JOIN ma le condizioni WHERE successive lo hanno fatto agire come un semplice [INNER] JOIN .
    Spostare le condizioni nella clausola JOIN per farlo funzionare come previsto. In questo modo, solo le righe che soddisfano tutte queste condizioni vengono unite in primo luogo (oppure le colonne della tabella di destra sono riempite con NULL). Come hai fatto tu, le righe unite vengono testate per condizioni aggiuntive virtualmente dopo la LEFT JOIN e rimosse se non passano, proprio come con un semplice JOIN .

  • COUNT() non restituisce mai NULL all’inizio. È un’eccezione tra le funzioni aggregate in questo senso. Pertanto, COALESCE(COUNT(col)) non ha mai senso, anche con parametri aggiuntivi. Il manuale :

    Va notato che, tranne che per il count , queste funzioni restituiscono un valore nullo quando non viene selezionata alcuna riga.

Grassetto enfasi mio.

Per chiarire,

la linea importante è GROUP BY MAIN_TABLE che gestirà il valore NULL da SOME_TABLE

 SELECT COUNT(ST.ID) FROM MAIN_TABLE MT LEFT JOIN SOME_TABLE ST ON MT.ID = ST.MT_ID GROUP BY MT.ID