Get common values from table using ruby

Question

My code is this

user_friends =Friend.find(:all, :select => "friend_id", :conditions => "app_user_id =12345")

second_user_friends = Friend.find(:all, :select => "friend_id", :conditions => "app_user_id = 123321")

common_friends_id = user_friends.map{|uf| uf.friend_id}.to_a & second_user_friends.map{|suf| suf.friend_id}.to_a

common_friends = Friend.find(:all, :conditions => "friend_id in (#{common_friends_id.join(",")}) 
  and app_user_id = 12345")

it takes very much time can i do in a sort way please help me

which query from above is slow? check if you have an _index_ on `app_user_id` in the `friends` table — pungoyal, May 09 '13 at 08:32

shweta · Accepted Answer · 2013-05-09T13:55:39.087

1

Try

 ActiveRecord::Base.connection.select_all("SELECT f1.* FROM friends f1 
         JOIN friends p2 ON f2.friend_id = f1.friend_id 
         WHERE f1.app_user_id = 12345 and f2.app_user_id = 123321")

For Rails 2

Friend.all(:joins=>"JOIN friends f2 ON f2.friend_id = friends.friend_id",
       :conditions =>"friends.app_user_id = 12345 and f2.app_user_id = 123321")

For Rails 3

Friend.where("friends.app_user_id = 12345 and f2.app_user_id = 123321")
      .joins("JOIN friends f2 ON f2.friend_id = friends.friend_id")

edited May 09 '13 at 13:55

answered May 09 '13 at 08:19

shweta

8,019
1
40
43

slightly messy. won't a self join be better, something like here: http://stackoverflow.com/questions/3642005/self-join-on-a-table-with-activerecord ? – pungoyal May 09 '13 at 09:24
Isn't `all` with params isn't deprecated? You should use `join` and `where`. – Hauleth May 09 '13 at 12:11

score 0 · Answer 2 · answered May 09 '13 at 08:14

I think this should be enough.

second_user_friend_ids = Friend.find(:all, :select => "friend_id", :conditions => "app_user_id = 123321").map(&:id)

common_friends = Friend.find(:all, :conditions => "friend_id in (#{second_user_friend_ids.join(",")}) and app_user_id = 12345")

Get common values from table using ruby

2 Answers2