Compare associations between domain objects in Grails

Question

I am not sure if I am going about this the best way, but I will try to explain what I am trying to do.

I have the following domain classes

class User { static hasMany = [goals: Goal] }

So each User has a list of Goal objects. I want to be able to take an instance of User and return 5 Users with the highest number of matching Goal objects (with the instance) in their goals list.

Can someone kindly explain how I might go about doing this?

Answer 1

The easiest and most efficient way to achieve this is using plain SQL. Assuming you have these tables

users      [id]
goals      [id, description]
user_goals [user_id, goal_id]

You can have the following query to do what you need:

set @userId=123;
select user_id, count(*) as matched from user_goals
where user_id!=@userId
  and goal_id in (select ug.goal_id from user_goals ug where ug.user_id=@userId)
group by user_id order by matched desc limit 5;

This takes a user id and returns a list of other users with matching goals, sorted by the number of matches. Wrap it up in a GoalService and you're done!

class GoalService {
  def findUsersWithSimilarGoals(user) {
    // ...
  }
}

It may also be possible to do this with criteria or HQL, but with queries like this it's usually easier to use SQL.

Answer 2

If you're looking for a simple match, perhaps the easiest way would be to do a findAll for each Goal and then count the number of results that each other User appears in:

Map user2Count = [:]
for (goal in myUser.goals){
    for (u in User.findAllByGoal(goal)){
         def count = user2Count.containsKey(u) ? user2Count.get(u) : 0
         count++
         user2Count.put(u, count)
    }
}
// get the top 5 users
def topUsers = user2Count.entrySet().sort({ it.value }).reverse()[0..5]

This may be too slow, depending on your needs, but it is simple. If many users share the same goals then you could cache the results of findAllByGoal.