CODEWITHSUNDEEP
phpmysql
Ivan Larenas
Ivan Larenas

Reputation: 3

how to find users matching eachother when the data used to match is stored on 3 different tables

its my first time asking here

so basically i have 3 tables one storing customer basic data, one storing user attributes based on questions and another storing user prefferences which stoes multiple options of the same attributes.

lets say...

TABLE users:

userid, username, useremail, userphone

TABLE attributes:

attributeid,attributeofuser, attributename(number), attribute value(number)

TABLE prefferences:

preffid, preffofuser, preffattributename(number), preffattributevalue(number)

the attributename and attributevalues are numbers of the id of the attributes and valuesstored in another table.

if preffattributevalue is 0 means it doesnt matter so any attribute is ok

so what i need is to find all users that match the prefferences of a specific user based on their attributes and do it by percentage too , so not only 100% matches also less.

i have acomplished that doing loops of querys which looks very heavy and looking for a better option. thanks in advance

Upvotes: 0

Views: 368

Answers (2)

luksch
luksch

Reputation: 11712

As Marty McVry said, this is an interesting query. Here is my solution. Note that I took the liberty of renaming your tables a bit...

The schema:

CREATE TABLE user (id int, username varchar(200), info varchar(200));
CREATE TABLE attr (id int, user_id int, a_name varchar(200), a_value varchar(200));
CREATE TABLE pref (id int, user_id int, pa_name varchar(200), pa_value varchar(200));

The query: 

SELECT looker.username AS looker_name, 
    CONCAT("likes ",match_candidate.username,"\'s") AS candidate_name, 
    GROUP_CONCAT(" ", CONCAT(attr.a_value," ", attr.a_name)) AS attributes,
    COUNT(attr.a_name) AS match_count
FROM pref 
  INNER JOIN attr ON attr.a_name = pref.pa_name 
  INNER JOIN user AS match_candidate ON match_candidate.id = attr.user_id
  INNER JOIN user AS looker ON looker.id = pref.user_id
WHERE (pref.pa_value IS NULL OR pref.pa_value = attr.a_value) AND
  pref.user_id = 101 AND looker.id <> match_candidate.id
GROUP BY candidate_name
ORDER BY match_count DESC;

Explanation:

We take the pref table and join it with the attr table to get the matching attribute names. The attr table can be joined with the user table to get the name of the candidates. Finally we can make the connection to the "looker", i.e. the user that wants his/her preferences matched. This is done by the second join with the user table but this time of course on the pref.user_id. Now we need to sort out the attribute values that don't match. We do this by enforcing that all result rows have either NULL as preference attribute value or that that attribute value equals the attribute value of the matching candidate (attribute names already match by the join condition). To get the matching candidates of a specific "looker" we must also include this in the WHERE clause (pref.user_id = 101). Here 101 is just the example of one user's id. Finally we must make sure that we don't match the looker with him/herself (in case own preferences match own attributes). The SELECT fields are just a bit of toying around with GROUP_CONCAT and CONCAT To combine the matching attributes we use GROUP BY.

I made a sqlfiddle to play around with. So in my example I used strings as attribute names and values. NULL in pref value means no preference means all attribute values of candidates match.

Side note: You could use the same table for attr and pref and adding a field defining its type.

Update side note: I updated this answer now quite a bit. If you checked out earlier versions of the fiddle, please follow the link again, because it may have changed.

Upvotes: 1

Marty McVry
Marty McVry

Reputation: 2856

Interesting problem...

Below query is untested, so not sure if it'll work.

    SELECT u.*,
           sub.num_match
      FROM users AS u
INNER JOIN (   SELECT a.attributeofuser AS uid,
                      COUNT(a.attributeid) AS num_match
                 FROM preferences AS p
            LEFT JOIN attributes AS a
                   ON p.preffattributename = a.attributename
                WHERE p.prefofuser = $user_id
                  AND p.prefofuser <> a.attributeofuser
             GROUP BY a.attributeofuser                      ) sub
        ON u.userid = sub.uid
  ORDER BY sub.num_match DESC;

First, the subquery gets all userid's of users having at least 1 attribute in common with the requesting user's preferences. It will also return the number of attributes they have in common, so you can calculate the match percentage later on.

Using the data from the subquery, you can then fetch the user data from your users-table.

Use another query to fetch the number of preferences the requesting user has put in and you can calculate the match percentage!

Upvotes: 0

Related Questions