Geek asks and answers

Compare the similarities between the two result sets.

I am creating a music site where I would like users to be able to find users who love about the same artists as they are.

I have a "like" table that has 2 columns "id_user", "id_artist". Here is an example of how I would like to work:

User 1 likes:
1, 12
1, 13
1, 14
1, 26
1, 42
1, 44

User 2 likes:
2, 13
2, 14
2, 15
2, 26
2, 42
2, 56

These 2 users have 4 artists. Is there a way to compare these 2 result sets to find the most similar people in the database?

My first idea was to combine sympathies this way: “12,13,14,26,42,44” in a line and use mysql FULLTEXT evaluations to compare different lines. This did not work ... I do not know why, but mysql fulltext only works with text ... not with numbers ...

Any idea or any clue would be greatly appreciated.

+3
2

- :

SELECT first_user.id_user, second_user.id_user, COUNT(first_user.id_user) AS total_matches

FROM likes AS first_user

JOIN likes AS second_user
ON second_user.id_artist = first_user.id_artist
AND second_user.id_user != first_user.id_user

GROUP BY first_user.id_user, second_user.id_user

ORDER BY total_matches DESC

LIMIT 1

, . - " ", LIMIT 1. . cron .

:

CREATE TABLE IF NOT EXISTS `likes` (
  `id_user` varchar(50) DEFAULT NULL,
  `id_artist` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `likes` (`id_user`, `id_artist`) VALUES ('8', '39'), ('8', '37'), ('4', '37'), ('8', '24'), ('8', '7'), ('4', '28'), ('8', '28'), ('4', '27'), ('4', '11'), ('8', '49'), ('4', '7'), ('4', '40'), ('4', '29'), ('8', '22'), ('4', '29'), ('8', '11'), ('8', '28'), ('4', '7'), ('4', '31'), ('8', '42'), ('8', '25'), ('4', '25'), ('4', '17'), ('4', '32'), ('4', '46'), ('4', '19'), ('8', '34'), ('3', '32'), ('4', '21')

+---------+---------+---------------+
| id_user | id_user | total_matches |
+---------+---------+---------------+
| 8       | 4       |             7 |
+---------+---------+---------------+
+2

. ( "" , .)

, , "", , like . , 1 User 2, , . , , , 0.

, " ": , , n , n*(n-1)/2, n. , .

0

More articles:


All Articles