0

I'm looking for some help with a problem. I've a table with the following columns/fields -

COLUMNS = userName | userEmail | userInterests | .... and other fields

VALUE = userA | userA@email.com | 1,2,10

VALUE = userB | userB@email.com | 15,27,9,7,2

userName(varchar) = the primary key

userEmail(varchar) = user's email

userInterests(varchar) = comma separated numbers for interests. I've mapped these number to their actual value (interests) in PHP script. e.g. 1 - Painting, 2 - Dancing and so on...

Now I'm trying to find the users having the similar interests. The number of interests a user can have can vary. My goal is to find the best match for any user.

Suppose if User A has 3 interests and User B has 5 interests, from which 1 interest is common in both user, so I wanna get the matched user's name. Suppose if there's another user with 2 common interests, then in that case, return that profile. In General, I wanna find the best match (where all interests meet with other user), if not, then second best match (at least have 2 or 1 common interest).

I have already been to this solution but I'm unable to get the desired output. There's only one table, so I can't use the joins as well. SQL - Finding Users with Similar Interests

Please help me with this, I've been meddling with this from past 2 days and couldn't find the right solution. I tried my best to explain the problem, If I failed to do so, Please let me know and I can elaborate. Thanks

Satan
  • 3
  • 2
  • maybe [FIND_IN_SET](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set) or the use of REGEXP can help with finding occurences? In combination with subqueries of the same table. – Sam020 Aug 24 '21 at 09:37
  • Fix your data model! Storing numbers in strings is wrong! Not declaring foreign key relationships is wrong! Storing multiple values in one string column is wrong! – Gordon Linoff Aug 24 '21 at 11:11

1 Answers1

0

I think the best approach would be to first normalize your database so you don't have an array like structure. This would make this type of query more efficient and much easier to do.

If that's not an option, you can have a look at this answer of how to do this normalization via query to a temporary table and work from there. Once you have a separate table for interests, you can proceed with the solution you already pointed in your question, making a count of the occurences.

Alcsaw
  • 26
  • 3