0

We recently installed a "like" extension on our phpbb message board, which is stored in a table we'll call "likes". I've been struggling to construct a query I've called "admirer", where it counts how many likes one user has given another. The output would ideally look like the following:

user_id_to | like count | user_id_from
---------------------------------------
    sam    |      6     |      mike
    sam    |      3     |      john
    mike   |      4     |      sam
    john   |      5     |      mike

Hopefully that helps. I have a query where it counts how many likes users get from a specified user_id in the WHERE clause as shown below:

SELECT users.username, COUNT(likes.like_id) AS love
FROM likes LEFT JOIN users ON users.user_id = likes.user_id_to
WHERE likes.user_id_from = [someone's user id]
GROUP BY users.username
ORDER BY love DESC;

However, I'm struggling to modify it without the WHERE, and have it display the count of likes from any user to any user. Any suggestions would be greatly appreciated.

  • what is the structure of your "likes" table? – Jehad Keriaki Oct 28 '16 at 18:16
  • like_id, user_id_from, user_id_to, like_time are the relevant attributes – sxmrxzxr Oct 28 '16 at 18:54
  • Get rid of id, you have a 'natural' PRIMARY KEY(user_id_from, user_id_to). – Rick James Oct 28 '16 at 20:50
0

Two things:

  1. Group by both user IDs
  2. Inner join to the users' table two times, use different alias to join it with different user id

    SELECT u_to.username as user_to, u_from.username as user_from, count(*) as like_count 
    FROM likes
    INNER JOIN users u_to on likes.user_id_to=u_to.user_id
    INNER JOIN users u_from on likes.user_id_from=u_from.user_id
    GROUP BY u_to.username, u_from.username
    ORDER BY like_count DESC
    
| improve this answer | |
  • perfect, much appreciated – sxmrxzxr Oct 28 '16 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.