Rails Arel，从同一列中选择但是多个值

I have a table called cherry_picker_cherries. These records polymorphically belong to a model called Pickable. They can have multiple entries in the table for the same pickable, but it always has a different cherry_pick_id.

我有一个名为cherry_picker_cherries的表。这些记录多态地属于名为Pickable的模型。它们可以在表中有多个条目用于相同的pickable,但它总是有不同的cherry_pick_id。

My problem: I have a cherry pick called Favorite Tree and a separate cherry_pick called Favorite Fruit. I want to be able to filter a collection of pickables by multiple cherry picks. For example, I want to find all pickables that have a favorite tree of cherry AND have a Favorite Fruit of orange.

我的问题:我有一个名为Favorite Tree的樱桃选择和一个名为Favorite Fruit的单独cherry_pick。我希望能够通过多个樱桃选择来过滤一系列的选择。例如,我想找到所有喜欢的樱桃树,并且有一个最喜欢的橘子果实。

I can accomplish this in a terrible manner using the following:

我可以使用以下方法以可怕的方式完成此任务:

CherryPicker::Cherry.find_by_sql("SELECT * from cherry_picker_cherries t1 JOIN cherry_picker_cherries t2 USING(pickable_id) WHERE t1.value = 'orange'AND t2.value = 'cherry'")

CherryPicker :: Cherry.find_by_sql(“SELECT * from cherry_picker_cherries t1 JOIN cherry_picker_cherries t2 USING(pickable_id)WHERE t1.value ='orange'AND t2.value ='cherry'”)

However, there are several drawbacks to this approach. It doesn't handle even more Cherry Picks, only two right now. Also, the resultant set is converted into an array, but I need to return an ActiveRecord relation so that I can continue to chain methods onto it.

但是,这种方法有几个缺点。它不能处理更多Cherry Picks,现在只有两个。此外,结果集转换为数组,但我需要返回一个ActiveRecord关系,以便我可以继续将方法链接到它上面。

I was trying to see if AREL had some kind of solution to generate a simple query that would match the above, but I am stuck at a dead end. It would be easy enough to loop over the hash of parameters to allow for 2,3, 4 or X number of cherry picks that you could filter by.

我试图看看AREL是否有某种解决方案来生成一个与上述相符的简单查询,但我陷入了死胡同。循环遍历参数的哈希很容易,允许你可以过滤2,3,4或X个樱桃选择。

Is this even possible in AREL, rails, or anything else?

这在AREL,rails还是其他任何东西都可能吗?

2 个解决方案

#1

Can you give this a shot?

你能试一试吗?

CherryPicker::Cherry.joins("join cherry_picker_cherries t2 using(pickable_id)").where("cherry_picker_cherries.value = ? and t2.value = ?", 'orange', 'cherry')

CherryPicker :: Cherry.joins(“使用(pickable_id)加入cherry_picker_cherries t2”)。其中(“cherry_picker_cherries.value =?和t2.value =?”,“orange”,“cherry”)

If this works, you can iterate over the fruits you want to join and do another joins with an incremented variable name (t3, t4.. etc) because the sql query will not be executed until it's needed.

如果这样做,你可以遍历你想要连接的水果,并使用递增的变量名称(t3,t4 ......等)进行另一次连接,因为在需要之前不会执行sql查询。

Here's how to join the same table on itself multiple times:

以下是如何多次连接同一个表:

arr = ['orange', 'cherry', 'apple']

query = CherryPicker::Cherry.joins("join cherry_picker_cherries t0 using(pickable_id)")

arr.drop(2).each_with_index do |e,i|
  query = query.joins("join cherry_picker_cherries t#{i+1} using(pickable_id)")
end

You can do the same to generate your wheres

你可以做同样的事情来生成你的孩子

It would be great if you could suggest edits so I can improve this post as it's really hard to test without having your tables =)

如果您可以建议编辑,这将是很好的,所以我可以改进这篇文章,因为它很难测试,没有你的表=)

#2

The following solution is actually part of the larger problem, but I did manage to make it work using the bits and pieces you gave me:

以下解决方案实际上是更大问题的一部分,但我确实设法使用您给我的点点滴滴使其工作:

def self.with_cherries(params={})
  # See also: http://*.com/questions/21689795/rails-arel-select-from-same-column-but-multiple-values
  result = all
  if (params.keys.length > 1)
    # filtering by 2 or more cherry picks
    result = result.joins(:cherries).where("cherry_picker_cherries.pickable_type = ?", self.name)

    # Setup the complicated joins
    params.each_with_index do |cherry_pick_and_value, index|
      result = result.joins("JOIN cherry_picker_cherries t#{index} USING(pickable_id)") unless index.zero?
    end

    my_query_string = ""
    params.values.each_with_index do |k, i|
      if i.zero?
        my_query_string << "cherry_picker_cherries.value = ?"
      else
        my_query_string << " AND t#{i}.value = ?"
      end
    end
    sanitized_string = self.sanitize_sql_array([my_query_string, params.values].flatten)
    result = result.where(sanitized_string).references(:cherries)
  else
    if params.present? && (params.keys.length == 1)
      # Only filtering by a single cherry pick
      result = result.with_cherry(params.first.first, params.first.last)
    end
  end
  result
end

#1