高级SQL Rails中 [英] Advanced SQL in Rails

问题描述

我有2个型号

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?

推荐答案

一种选择是把每一个名字为个别内部连接参数。在SQL它会是这样的：

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'

既然是内部联接，它只会显示其中的用户表可以与F1和F2被加入结果。

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

和使用它在Rails的，也许这样做事情是这样的：

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

这篇关于高级SQL Rails中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！