Hi all,
I need to sort users by matching preferences, and because of the high count of rows I need to do that in the data store, not in the business logic layer.
Imagine I have a table like
USER-PREFERENCE
1-A
1-B
1-C
2-A
2-C
3-B
3-C
3-D
4-C
4-E
I can't hardcode anything in this procedure, because the user preferences are going to be N and increasing with time.
And the primary key for this table is (userid,prefid, I use letters but they would be integers), it's a cartesian product.
I need to develop a stored procedure that returns a selection of rows given the user I want to use as comparison
getMatchingUsers(1)
USER MATCH
3- 2
2- 2
4- 1
that is the number of matching preferences for the users excluding the one that we are referring to. any idea on how to develop this? is this a known sql design pattern?
the steps would be:
- find the set of distinct user preferences for the given user (say 1): A,B,C
- for every preference in the set, loop through every user besides user 1 to find who has the same preference... for pref A you get: {2}, for pref B you get {3}, for pref D you get {2,3,4}
- somehow join this sets to get the total matches: user 2 -> 2 matches, user 3->2 matches, user 4-> 1 match, and order this set by matches number
TIA