I have a query:
with comments_info as (
SELECT p.post_id as post_id,
COUNT(p.id) as comments
FROM post_comments p
GROUP BY post_id
)
select p.id as post_id, c.comments
from posts p
left join comments_info c on c.post_id = p.id
But if there are no comments on the post, then count returns null.
I tried to do like this:
CASE WHEN COUNT(p.id) IS NULL THEN '0' ELSE COUNT(p.id) END as comments
but it still returns null.
Comments model:
id pk,
post_id fk,
description text,
Posts model:
id pk,
title varchar,
description text,
>Solution :
Try with COALESCE by wrapping your count, see
COALESCE(count(p.id),0) as comments
As per comment:
use COALESCE in the outer query:
COALESCE (p.comments, 0)