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 |
+------+-----------+-----------+-------------+
2 rows in set (0.00 sec)

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

MariaDB [sandbox]> select * from users where id < 9;
+----+----------+-----------+--------+---------------------+
| id | userName | photo     | status | ts                  |
+----+----------+-----------+--------+---------------------+
|  1 | John     | john.png  |      1 | 2016-12-08 13:14:24 |
|  2 | Jane     | jane.png  |      1 | 2016-12-08 13:14:24 |
|  3 | Ali      |           |      1 | 2016-12-08 13:14:24 |
|  6 | Bruce    | bruce.png |      1 | 2016-12-08 13:14:24 |
|  7 | Martha   |           |      1 | 2016-12-08 13:14:24 |
|  8 | Sidney   |           |      1 | 2016-12-08 13:14:24 |
+----+----------+-----------+--------+---------------------+
6 rows in set (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
    -> INNER JOIN USERS ON USERS.ID=posts.FROM_userid
    -> LEFT JOIN likes ON likes.target=posts.id
    -> LEFT JOIN(SELECT ID pl_id, USERNAME UNAME FROM USERS )plikers ON pl_id=likes.liker
    -> GROUP BY 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 |
+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+
2 rows in set (0.00 sec)

but you should be mindful of the caveat