How to create an SQL query for "Likes the common"
I am currently building a system (php and mysql) that in the user profile allows you to add "Favorite Music Artists" to the list. 'tried to figure out a way to compare the user with other users and return "recommended friends".
For example:
User A Likes
- 1
- 2
- 3
- 4
User B Likes
- 1 <- A likes
- 5
- 6
- 7
User C Likes
- 1 <- A likes
- 2 <- A likes
- 8
- 9
Then, with this user A, you get the following recommendation:
User C
User B
I guess for this I need to create a relational database and standardize most of the user input.
So my questions are: What is the best database structure for this kind of mapping? Which query should I use? (doesn't have to be exact)
source to share
Directly answer to your question, but you can check out the book Programming Collective Intelligence . Based on your question, I think you will find it very useful.
source to share
A simple implementation might look like this:
CREATE TABLE user_tbl(
user_id BIGINT,
...
)
CREATE TABLE music_tbl(
music_id BIGINT,
...
)
CREATE TABLE likes_tbl(
user_id BIGINT,
music_id BIGINT
)
To find all users who have a similar taste for a particular user, we do this:
select u1.user_id, u2.user_id, count(*) as weight from likes_tbl u1, likes_tbl u2
where u1.music_id = u2.music_id and u1.user_id <> u2.user_id and u1.user_id = @user_id
group by u1.user_id, u2.user_id
The weight column is the number of artists users have in the column, so the higher the weight, the more they have in common. Therefore, you can recommend the top 5 users with the highest weight.
This can be expanded in different ways. One possibility is to add genre_id to music_tbl and likes_tbl and then connect to genre_id.
source to share
Don't duplicate what has already been posted, but ...
--
-- Working MySQL implementation of a "user compatibility" schema.
--
DROP TABLE IF EXISTS favourite;
DROP TABLE IF EXISTS artist;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(32),
PRIMARY KEY (user_id)
);
CREATE TABLE artist (
artist_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(32),
PRIMARY KEY (artist_id)
);
CREATE TABLE favourite (
favourite_id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
artist_id INT NOT NULL,
UNIQUE (user_id, artist_id),
PRIMARY KEY (favourite_id),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
FOREIGN KEY (artist_id) REFERENCES artist (artist_id) ON DELETE CASCADE
);
INSERT INTO users
(name)
VALUES
("Alice"),
("Bob"),
("Carol"),
("Dave")
;
INSERT INTO artist
(name)
VALUES
("Jewel"),
("Sarah McLachlan"),
("Britney Spears"),
("David Bowie"),
("The Doors")
;
INSERT INTO favourite
(user_id, artist_id)
VALUES
(
(SELECT user_id FROM users WHERE name = "Alice"),
(SELECT artist_id FROM artist WHERE name = "Jewel")
),
(
(SELECT user_id FROM users WHERE name = "Alice"),
(SELECT artist_id FROM artist WHERE name = "Sarah McLachlan")
),
(
(SELECT user_id FROM users WHERE name = "Bob"),
(SELECT artist_id FROM artist WHERE name = "Jewel")
),
(
(SELECT user_id FROM users WHERE name = "Bob"),
(SELECT artist_id FROM artist WHERE name = "Sarah McLachlan")
),
(
(SELECT user_id FROM users WHERE name = "Bob"),
(SELECT artist_id FROM artist WHERE name = "Britney Spears")
),
(
(SELECT user_id FROM users WHERE name = "Bob"),
(SELECT artist_id FROM artist WHERE name = "David Bowie")
),
(
(SELECT user_id FROM users WHERE name = "Carol"),
(SELECT artist_id FROM artist WHERE name = "David Bowie")
),
(
(SELECT user_id FROM users WHERE name = "Carol"),
(SELECT artist_id FROM artist WHERE name = "The Doors")
),
(
(SELECT user_id FROM users WHERE name = "Dave"),
(SELECT artist_id FROM artist WHERE name = "Jewel")
),
(
(SELECT user_id FROM users WHERE name = "Dave"),
(SELECT artist_id FROM artist WHERE name = "The Doors")
)
;
SELECT
t0.user_id myuser,
t1.user_id friend,
COUNT(*)
FROM favourite t0
JOIN favourite t1 ON t1.artist_id = t0.artist_id
WHERE t0.user_id != t1.user_id
GROUP BY t0.user_id, t1.user_id;
--
-- The same thing, but returning names!
--
SELECT
t0u.name myuser,
t1u.name friend,
COUNT(*)
FROM favourite t0
JOIN favourite t1 ON t1.artist_id = t0.artist_id
JOIN users t0u ON t0u.user_id = t0.user_id
JOIN users t1u ON t1u.user_id = t1.user_id
WHERE t0.user_id != t1.user_id
GROUP BY t0.user_id, t1.user_id;
Good luck!
source to share
In SQL Server:
CREATE TABLE Users (
UserID BIGINT IDENTITY (1,1) NOT NULL
--Other columns here
)
CREATE TABLE Artists (
ArtistID BIGINT IDENTITY(1,1) NOT NULL
-- Other columns
)
CREATE TABLE FavoriteArtists (
UserID BIGINT, ArtistID BIGINT )
to select users with the same settings:
SELECT
FROM FavoriteArtists u, FavoriteArtists f
WHERE u.ArtistID = f.ArtistID AND u.UserID = @TARGET_USER AND f.UserID <> @TARGET_USER
source to share
Tables
User
userid int
FirstName varchar(30)
LastName varchar(30)
Song
songid int
Title varchar(30)
Artist varchar(30)
UserSong
userid
songid
Query
select User.userid, User.FirstName, User.LastName
from UserSong
inner join Song
on UserSong.songid=Song.songid
inner join User
on UserSong.userid=User.userid
where Song.Artist='Some Artist'
Less verbose query using natural join
select User.userid, User.FirstName, User.LastName
from UserSong
natural join Song
natural join User
where Song.Artist='Some Artist'
(Note that I haven't tested this one yet. Someone will correct me if I'm wrong.)
The above query will provide you with a list of all users who "love" a given artist. You can then use this list to show other users who still enjoy what they are doing.
source to share