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?

Sorry, I didn't read your question carefully enough... deleted my incorrect answer. — Mischa
– Mischa, Commented May 21, 2012 at 4:56
You misunderstood my correction to Mischa's misunderstanding, our comments were saying the same thing. — mu is too short
– mu is too short, Commented May 21, 2012 at 5:02
@muistooshort oh ya :) Any ideas how to go about this query? — Avinasha Shastry
– Avinasha Shastry, Commented May 21, 2012 at 5:11

DanneManne · Accepted Answer · 2012-05-21 08:23:31Z

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")

aristofun · Accepted Answer · 2012-05-21 08:18:54Z

1

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 2 friends.

This is the best solution i got when tried to solve similar problem for myself. If you find the way to do it in pure sql or ActiveRecord – let me know please!

answered May 21, 2012 at 8:18

aristofun

3753 silver badges18 bronze badges

Comments

Steve Jorgensen · Accepted Answer · 2012-05-21 16:23:01Z

Although using hard-coded SQL as suggested by DanneManne will most often work, and is probably the way you'd want to go, it is not necessarily composable. As soon as you have hard-coded a table name, you can run into problems combining that into other queries where ActiveRecord may decide to alias the table.

So, at the cost of some extra complexity, we can solve this using 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')))

This will use a pair of subqueries to do the job.

I'm fairly certain there's an ARel solution using joins as well, but and I can figure out how to compose that query in ARel, just not how to then use that query as the basis for an ActiveRecord query to get back User model instances.

This is great but this exact query is not working. Looking at other solutions using this technique. Thanks!

Collectives™ on Stack Overflow

Advanced SQL in Rails

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related