Rick Joe Rick Joe - 7 months ago 13
SQL Question

delete user comment but keep it - count

When an user deletes a post or a comment I just set is as 1 on

delete
column.

The problem is, I use a count to show user how many comments he has on the post.

select c.nome, p.foto, c.user, p.user, count(DISTINCT comentarios.id) as comentarios_count from posts p
join cadastro c on p.user=c.id
left join comentarios on comentarios.foto = p.id
where p.delete='0' and comentarios.delete='0'
group by p.id
order by p.id desc limit ?


the problem is comentarios.delete='0' will select only posts that has at least one comment. I want to select all posts, but only count the comments that
delete
is set to 0.
what is wrong?

Answer

How about selecting all and just subtracting the deletes

select c.nome, p.foto, c.user, p.user, count(DISTINCT comentarios.id) -sum(comentarios.delete) as comentarios_count from posts p 
join cadastro c on p.user=c.id 
left join comentarios on comentarios.foto = p.id
where p.delete='0' 
group by p.id
order by p.id desc limit ?