Mysql Get All Grouped Results One One Query With A Row Count

So I have searched everywhere for this but I can't find this specific relation. The query below returns all posts from a user and the number of people who liked the post because I

Solution 1:

If your model looks like this

MariaDB [sandbox]>select*from posts;
+------+-----------+-----------+-------------+| id   | post_body | post_type | from_userid |+------+-----------+-----------+-------------+|1| POST1     |NULL|1||2| POST2     |NULL|2|+------+-----------+-----------+-------------+2rowsinset (0.00 sec)

MariaDB [sandbox]>select*from likes;
+------+--------+-------+| id   | TARGET | liker |+------+--------+-------+|1|1|3||2|1|7||3|2|8||3|2|6|+------+--------+-------+4rowsinset (0.00 sec)

MariaDB [sandbox]>select*from users where id <9;
+----+----------+-----------+--------+---------------------+| id | userName | photo     | status | ts                  |+----+----------+-----------+--------+---------------------+|1| John     | john.png  |1|2016-12-0813:14:24||2| Jane     | jane.png  |1|2016-12-0813:14:24||3| Ali      ||1|2016-12-0813:14:24||6| Bruce    | bruce.png |1|2016-12-0813:14:24||7| Martha   ||1|2016-12-0813:14:24||8| Sidney   ||1|2016-12-0813:14:24|+----+----------+-----------+--------+---------------------+6rowsinset (0.00 sec)

Then as @1000111 suggests you can

MariaDB [sandbox]>SELECT  posts.id postid,posts.post_body,posts.post_type,POSTS.FROM_USERID
    ->  , USERS.USERNAME
    ->        ,GROUP_CONCAT(likes.liker) LIKER
    ->  ,likes.target
    ->  ,GROUP_CONCAT(plikers.pl_id) pl_id
    ->        ,GROUP_CONCAT(plikers.UNAME) pl_un
    ->  ,COUNT(posts.id) numberOflikes
    ->->FROM posts
    ->INNERJOIN USERS ON USERS.ID=posts.FROM_userid
    ->LEFTJOIN likes ON likes.target=posts.id
    ->LEFTJOIN(SELECT ID pl_id, USERNAME UNAME FROM USERS )plikers ON pl_id=likes.liker
    ->GROUPBY postid;
+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+| postid | post_body | post_type | FROM_USERID | USERNAME | LIKER | target | pl_id | pl_un        | numberOflikes |+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+|1| POST1     |NULL|1| John     |7,3|1|7,3| Martha,Ali   |2||2| POST2     |NULL|2| Jane     |6,8|2|6,8| Bruce,Sidney |2|+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+2rowsinset (0.00 sec)

but you should be mindful of the caveat