" People Who Liked this Also Liked " Query in Mysql PHP

Question

Music table

id | title
1    Rap God
2    Blank Space
3    Bad Blood
4    Speedom
5    Hit 'em up

Like table

u_id | m_id
1      1
1      2
1      4
1      5
2      3
2      4
2      5
3      1
3      5
4      1
4      2
4      5 

Now if someone visits music with m_id = 1

Then the output might be like

   m_id

    5
    2
    4

To explain this a bit...

As m_id = 1 is liked by users -> {1,3,4} which in turn likes ->{2,4,5} musics. Since m_id=5 is liked by max number of users its first followed by m_id = 2 and m_id = 4.

My Try

• I queried the users who liked m_id = 1

  SELECT u_id FROM likes WHERE m_id =1

• Then i stored in in an array and selected each of their likes and arranged them in desc order of count.

But it is a very slow and long process is there any way i can do this ?

p.s I have heard of Association Rules and Bayesian theorem can be user to achieve this. But can anyone help me out with an example ?

Answer 1

You can JOIN back on the Like table and do something like this.

SELECT also_like.m_id, COUNT(also_like.m_id)
FROM [like] AS did_like
JOIN [like] AS also_like ON
    also_like.u_id = did_like.u_id
    AND also_like.m_id != did_like.m_id
WHERE did_like.m_id = 1
GROUP BY also_like.m_id
ORDER BY COUNT(also_like.m_id)

Essentially you are getting a list of users who liked an item then getting a complete list of those user's likes excluding the item they just liked.

You can then add a HAVING clause or LIMIT to filter things down a bit more.

Answer 2

using a subquery ...

SELECT m_id, count(u_id) as Rank FROM `like`
WHERE u_id in
(
   SELECT u_id
   FROM `like`
   WHERE m_id = 1
)
AND m_id <> 1
GROUP BY m_id
ORDER BY Rank DESC

and optionally

LIMIT 0, 10

or how many "alsolikes" you want to display