show top 5 users on logged user dashboard based on preferences

Question

I want to show top 5 users on logged user dashboard based on following criteria:

users are opting following attributes in their profile:

Relationship preferences,
Hobbies,
Interests,
Language etc

More the preferences will match, highest priority to user's profile will be assigned

table_users

userid  ufname  ulname  gender
--------------------------------
1       test1   Test2   M
2       testF   TestF   F
3       testF1  TestF1  F
4       testF2  TestF2  F
5       testF5  TestF2  F

table_preferences

preference_id   user_preferences
--------------------------------
1               Cooking
2               Gardening
3               Smoking
4               Single
5               widow
6               traveling

table_user_preferences

userid  user_preference_id
--------------------------------
1       1
1       2
1       3
1       4

2       3
2       4

3       2
3       3
3       6

4       1

5       1
5       2
5       3
5       4
5       6

Now, suppose user one is logged in, on his dashboard other registered users (with opposite i.e. gender female) will get display whose highest attributes are matching. As we can see, user five 5 matches most of the attributes so it will be display on top of list and rest will be get display accordingly as follows:

Result required:

User_id ufname
5       testF5
3       testF1
2       testF
4       testF2

My Query is I am thinking to use relevance search. Will it be suitable. Anyone can help me any suggestions.

Hello. And whats your problem? Stackoverflow is here to help you with specific programming problems, not to code for you. http://stackoverflow.com/help/how-to-ask — Twinfriends, Mar 10 '17 at 08:21
Can you provide us your code? We can't help you without you providing us your code. We can't know how you coded, so we can't know what errors you did. — Twinfriends, Mar 10 '17 at 09:09
I am not asking for a solution for that. If you have read my message carefully I have asked for suggestions and not solutions. — Atul Sharma, Mar 10 '17 at 10:38
`My Query is I am using relevance search and it is not giving me the right results` why you don't simply show us your query? — Twinfriends, Mar 10 '17 at 10:47

JYoThI · Answer 1 · 2017-03-10T12:18:29.663

Simple use MYSQL JOIN and SUBQUERY

1) Use subquery to get the preference of logged in user.

2) Then join table_user_preferences with table_users with ON tu.userid = tup.userid and AND tu.gender !=$current_user_gender and IN( ) condition to match the prefrence with logged in user preference.

3) Finally group the user and apply order by count of match preference

    select tu.*,tup.userid,count(1) as total_match 
    from table_user_preferences as tup 
    join table_users as tu
    ON tu.userid =tup.userid
    where tup.user_preference_id 
    IN (select up.user_preference_id from table_user_preferences as up where up.userid=$current_userid) 
    AND tu.gender !=$current_user_gender
    group by tup.userid order by total_match desc limit 5

show top 5 users on logged user dashboard based on preferences

1 Answers1