How to join on the same table multiple times?

Question

I'm querying for the mutual friends of a given two users. The query below should do the trick for the most part and the friendship table should be self-evident, containing a user_id and friend_id.

SELECT `users`.* FROM `users`
INNER JOIN `friendships` `a` ON `users`.`id` = `a`.`friend_id`
INNER JOIN `friendships` `b` ON `users`.`id` = `b`.`friend_id`
WHERE `a`.`user_id` = 1 AND `b`.`user_id` = 2

What's got me confused is how to write this semantic ActiveRecord. With ActiveRecord you can join on an association, but only once. So how do you go about writing this as plainly as possible in ActiveRecord?

Answer 1

I do it with string arguments to joins:

User.
  joins("INNER JOIN friendships a ON users.id = a.friend_id").
  joins("INNER JOIN friendships b ON users.id = b.friend_id").
  where("a.user_id" => 1, "b.user_id" => 2)

I'm not aware of a higher-level way to do such a join with Active Record.