How to search in rails from two models?

Fastest way to search two models connected via join table in rails given large data set

• I have a user model and a cd model connected through a join table 'cds_users'. I'm trying to return a hash of users plus each cd they have in common with the original user. @user.users_with_similar_cds(1,4,5) # => {:bob => [4], :tim => [1,5]} Is there a better/faster way of doing this without looping so much? Maybe a more direct way? def users_with_similar_cds(*args) similar_users = {} Cd.find(:all, :conditions => ["cds.id IN (?)", args]).each do |cd| cd.users.find(:all, :conditions => ["users.id != ?", self.id]).each do |user| if similar_users[user.name] similar_users[user.name] << cd.id else similar_users[user.name] = [cd.id] end end end similar_users end [addition] Taking the join model idea, I could do something like this. I'll call the model 'joined'. def users_with_similar_cds(*args) similar_users = {} Joined.find(:all, :conditions => ["user_id != ? AND cd_id IN (?)", self.id, args]).each do |joined| if similar_users[joined.user_id] similar_users[joined.user_id] << cd_id else similar_users[joined.user_id] = [cd_id] end end similar_users end Would this be the fastest way on large data sets?

• Answer:

  You could use find_by_sql on the Users model, and Active Record will dynamically add methods for any extra fields returned by the query. For example: similar_cds = Hash.new peeps = Users.find_by_sql("SELECT Users.*, group_concat(Cds_Users.cd_id) as cd_ids FROM Users, Cds_Users GROUP BY Users.id") peeps.each { |p| similar_cds[p.name] = p.cd_ids.split(',') } I haven't tested this code, and this particular query will only work if your database supports group_concat (eg, MySQL, recent versions of Oracle, etc), but you should be able to do something similar with whatever database you use.