Advanced SQL in Rails

Question

I have 2 models

class User < AR
 has_many :friends
end

class Friend < AR
  # has a name column
end

I need to find all Users who are Friends with both 'Joe' and 'Jack'

Any idea how i can do this in rails?

Answer 1

One option is to put each of the names as arguments for individual INNER JOINS. In SQL it would be something like this:

SELECT users.* FROM users
INNER JOIN friends AS f1 
    ON users.id = f1.user_id 
    AND f1.name = 'Joe'
INNER JOIN friends AS f2 
    ON users.id = f2.user_id 
    AND f2.name = 'Jack'

Since it is INNER JOINS, it will only display results where the users table can be joined with both f1 and f2.

And to use it in Rails, maybe do it something like this:

class User < AR
  has_many :friends

  def self.who_knows(*friend_names)
    joins((1..friend_names.length).map{ |n| 
      "INNER JOIN friends AS f#{n} ON users.id = f#{n}.user_id AND f#{n}.name = ?" }.join(" "),
      *friend_names)
    })
  end
end

Which you then can call like this:

@users = User.who_knows("Joe", "Jack")