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 on rails?

Answer 1

One option is to put each of the names as arguments for individual INNER JOINS. In SQL, it will 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 this is INNER JOINS, it will display only those results in which the user table can be joined with both f1 and f2.

And 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 can then call as follows:

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

Answer 2

Possible way: User.all(:joins => :friends, :conditions => ["friends.name IN (?,?)", "Joe", "Jack"], :group => "users.id") and then iterate over the array to find users with two friends.

This is the best solution I received when I tried to solve a similar problem for myself. If you find a way to do this in pure sql or ActiveRecord - let me know, please!

Answer 3

Although using the hard-coded SQL suggested by DanneManne will most often work, and probably the way you would like to go, it is not necessarily complicated. Once you have a hard-coded table name, you may run into problems combining this with other queries in which ActiveRecord can select a table alias.

So, due to some additional complexity, we can solve this problem with some AREL as follows:

 f = Friend.arel_table User. where(:id=>f.project(:user_id).where(f[:name].eq('Joe'))). where(:id=>f.project(:user_id).where(f[:name].eq('Jack'))) 

A couple of subqueries will be used to complete the task.

I'm pretty sure that the AREL solution also uses connections, but I can understand how to compose this request in ARel, and not how to use this request as the basis for an ActiveRecord request to return the user. reference specimens.