MySQL Preference Finder

Question

I have a large list of preferences for users in a MySQL database. For example,

User 2 - Preference 1 - yes
User 3 - Preference 3 - no
User 2 - Preference 3 - no
User 6 - Preference 2 - yes
User 3 - Preference 1 - no

Basically, I am trying to design a query that will be able to plug in a user and it will find users with similar preferences. For example, plugging in "user 2" into the above, it would return "user 3" because they share the no preference of #3. The query should find multiple similar users and factor in all of the preferences.

How would I do something like that using MySQL and PHP (if needed)? Anything helps!

Thanks!

So you have three tables described here. What's your current schema? — tadman, May 30 '16 at 17:49
@tadman. There should be 3 tables, but this is strictly a single table with three columns, two of which could be foreign keys. — Mad Physicist, Jun 01 '16 at 20:26

score 0 · Answer 1 · edited May 23 '17 at 12:07

For the sake of simplicity, I have defined the a simple table, similar to what you described in the question, like this:

CREATE TABLE pref (
  user integer not null,
  pref integer not null,
  value bool default false,
  primary key (user, pref)
);

Moreover, I added the following sample data:

INSERT INTO pref VALUES
(2, 1, true), (3, 3, false), (2, 3, false), 
(6, 2, true), (3, 1, false);

The principle idea of "similar preferences" is based on the concept of a self-join, i.e. joining the preference DB table with itself. After playing around with it, I quickly came to the following SELECT statement:

SELECT leftpref.user as leftuser, rightpref.user as rightuser, count(*) as score from pref leftpref 
INNER JOIN pref rightpref on leftpref.pref = rightpref.pref and leftpref.value = rightpref.value
WHERE leftpref.user <> rightpref.user
GROUP BY leftpref.user, rightpref.user
;

This will yield the following result:

leftuser rightuser score
2        3         1
3        2         1

which is exactly what is expected:

User 2 shares one preference with user 3
The second line is the symmetrical statement that user 3 shares a preference with user 2.
The more preferences two users are sharing, the higher the score will be

"Plugging in a user" would be to add another AND leftuser = <userid> to that query.

MySQL Preference Finder

1 Answers1