gSjwYRUh

11 like - 2 comment
22 likes - 4 comments (user has 2, but will show 4)
33 likes - 6 comments (user has 2)
4	
5select c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, count(DISTINCT likes.user) as likes_count, SUM(CASE comentarios.delete WHEN 0 THEN 1 ELSE 0 END)
6as comentarios_count, count(DISTINCT l2.user) as count2 from posts p 
7join cadastro c on p.user=c.id 
8left join profile_picture pp on p.user = pp.user
9left join likes on likes.post = p.id // here?
10left join comentarios on comentarios.foto = p.id
11left join likes l2 on l2.post = p.id and l2.user = '1'
12group by p.id
13order by p.id desc limit 10
14	
15CREATE TABLE IF NOT EXISTS `likes` (
16  `user` int(11) UNSIGNED NOT NULL,
17  `post` int(11) UNSIGNED NOT NULL,
18  `data` datetime NOT NULL,
19  UNIQUE KEY `user_post` (`user`,`post`),
20  FOREIGN KEY (`post`) REFERENCES posts (`id`) ON DELETE CASCADE
21) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
22
23CREATE TABLE IF NOT EXISTS `comentarios` (
24  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
25  `foto` int(11) UNSIGNED NOT NULL,
26  `user` int(11) UNSIGNED NOT NULL,
27  `texto` varchar(3000) NOT NULL,
28  `data` datetime NOT NULL,
29  `ip` varchar(20) NOT NULL,
30  `delete` tinyint(1) NOT NULL DEFAULT '0',
31  PRIMARY KEY (`id`),
32  FOREIGN KEY (`foto`) REFERENCES posts (`id`) ON DELETE CASCADE
33) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;