CODEWITHSUNDEEP
mysql
RGS
RGS

Reputation: 4253

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?

Upvotes: 0

Views: 64

Answers (2)

M.mhr
M.mhr

Reputation: 1759

You can use conditional sum instead of count, like this :

select c.nome, p.foto, c.user, p.user,
SUM(CASE comentarios.Deleted WHEN 0 THEN 1 ELSE 0 END)
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 ?

It will count the comments have not deleted and seems faster than calculating COUNT and SUM for each post

Upvotes: 1

Summer-Sky
Summer-Sky

Reputation: 491

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 ?

Upvotes: 1

Related Questions