Select ruby complex on Rails

Question

I have a model that describes the friendship between my users:

#CreateFriendship.rb
class CreateFriendships < ActiveRecord::Migration
  def change
    create_table :friendships do |t|
      t.integer :user_id
      t.integer :friend_id

      t.timestamps null: false
    end

    add_index :friendships, :user_id
    add_index :friendships, :friend_id
  end
end

#friendship.rb
class Friendship < ActiveRecord::Base
  belongs_to :user, :foreign_key => :user_id, class_name: 'User'
  belongs_to :friend, :foreign_key => :friend_id, class_name: 'User'

  scope :involving, -> (user) do
    where("friendships.user_id =? OR friendships.friend_id =?",user.id, user.id)
  end

  scope :sponsors, -> (user_1, user_2) do
     #o que fazer? Para retornar todos os usuarios que são amigos de   ambos usuarios;
  end

  scope :between, -> (user_id,friend_id) do
    where("(friendships.user_id = ? AND friendships.friend_id =?) OR (friendships.user_id = ? AND friendships.friend_id =?)", user_id,friend_id, friend_id, user_id)
  end
end

in a method of my controller need all friends in common between the two users, as I do in my scope :sponsors?

Answer 1

I was able to do this by moving Scope to my user model, and I did select it as follows:

scope :sponsors, -> (user, yell_user) do
  find_by_sql("SELECT users.id, users.name, users.avatar FROM users JOIN (SELECT user_id FROM friendships WHERE friend_id = #{user.id} INTERSECT SELECT user_id from friendships WHERE friend_id = #{yell_user.id}) AS f ON (f.user_id = users.id)")
end

I hope you can help someone.