Rails 4和Mongoid:以编程方式构建具有多个AND和OR条件的查询 [英] Rails 4 and mongoid: programmatically build a query with multiple AND and OR conditions

问题描述

我希望能够动态转换输入哈希，如下所示:

I want to be able to dynamically transform an input hash like the following:

{ name: ['John', 'Luke'], status: ['ACTIVE', 'SUSPENDED'] }

在查询中，将把不同的字段(名称，状态)置于AND关系中，并将每个字段的值置于OR关系中.在SQL中，可能是这样的:

in a query that will put the different fields (name, status) in a AND relationship, and the values for each field in a OR relationship. In SQL that would be something like this:

SELECT * 
FROM my_class 
WHERE (name LIKE 'John' OR name LIKE 'Luke') 
AND (status LIKE 'ACTIVE' OR status LIKE 'SUSPENDED')

我尝试了不同的方法，但是有点卡住了.这适用于具有多个值的一个字段:

I've tried different approaches but I'm kind of stucked. Here what works for one field with multiple values:

def search(criteria)
    result = MyClass.all

    criteria.each do |field, values|
      values = [values] unless values.respond_to?(:each)

      conditions = []
      values.each do |v|
        conditions << { field => v } unless v.empty?
      end

      result = result.or(conditions) # this is enough for one field only, but how to create an AND condition with the second field (and so on)?
    end

    result
  end

我已经读过有关any_of的信息，但我尚不清楚如何使用它.

I've read about any_of but it's not completely clear to me how to use it.

推荐答案

您要构建的查询是:

MyClass.where(
  :name.in   => [ 'John', 'Luke' ],
  :status.in => [ 'ACTIVE', 'SUSPENDED' ]
)

A :field.in的作用与SQL中的field in (...)相同，这只是or语句的缩写.这使事情变得相当容易，因为您只需将.in调用添加到其值是数组的criteria键中，就像这样:

A :field.in works the same as field in (...) in SQL and that's just a short form for an or-statement. That makes things quite a bit easier as you just have to add .in calls to the criteria keys whose vales are arrays, something like this:

query = criteria.each_with_object({}) do |(field, values), query|
  field = field.in if(values.is_a?(Array))
  query[field] = values
end
MyClass.where(query)

这篇关于Rails 4和Mongoid:以编程方式构建具有多个AND和OR条件的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！