[FIXED] How to get both like and dislike count of the post in single query using cake php or php

Issue

I have researched a day but I not get the solution.if it is possible to get he likes and dislike count of all post using single CakePHP query table structure

id user_id  posts_id  like_and_dislike
1    1         1          1
2    2         1          1
3    4         1          0
4    5         2          1

Here we need to get the like and dislike count of all posts that is for post 1 like -> 2 and dislike -> 1 And for post - 2 like -> 1 and dislike -> 0 need to list the count.please any one help on this query if it not possible please help in php query

Solution

I'm guess you need this

SELECT post_id, 
COUNT( CASE WHEN like_and_dislike = 1 THEN 1 END ) AS like_cnt, 
COUNT( CASE WHEN like_and_dislike = 0 THEN 1 END ) AS dislike_cnt 
FROM your_table
GROUP BY post_id
-- ORDER BY something
-- LIMIT x, y

Answered By - Oto Shavadze