Rails where 查询链与或用于数组输入 [英] Rails where query chain with or for array input

问题描述

我想创建一个查询链，以便在给定特定过滤器的情况下找到我的机器人用户.一些过滤器可以有多个值.例如，我的区域设置"过滤器可以有多个值(例如 fr_FR、en_US).

I want to create a query chain in order to find my bot users given specific filters. Some filters can have multiple values. For example, my "locale" filter can have multiple values (fr_FR, en_US for example).

在此示例中，我选中了两个区域设置复选框(fr_FR 和 en_US).

In this example, I check two locales checkboxes (fr_FR and en_US).

我创建了一个查询链，但输出不是我想要的:

I created a query chain but the output is not what I want:

SELECT "bot_users".* FROM "bot_users" WHERE ("bot_users"."core_bot_id"= ?AND (locale = 'fr_FR') OR "bot_users"."core_bot_id" = ?AND (locale = 'fr_FR') AND (locale = 'en_EN')) [["core_bot_id", 1],["core_bot_id", 1]]

SELECT "bot_users".* FROM "bot_users" WHERE ("bot_users"."core_bot_id" = ? AND (locale = 'fr_FR') OR "bot_users"."core_bot_id" = ? AND (locale = 'fr_FR') AND (locale = 'en_EN')) [["core_bot_id", 1], ["core_bot_id", 1]]

我想要这样的东西:

SELECT "bot_users".* FROM "bot_users" WHERE ("bot_users"."core_bot_id"= ?AND (locale = 'fr_FR' OR 'en_EN')) [["core_bot_id", 1]]

SELECT "bot_users".* FROM "bot_users" WHERE ("bot_users"."core_bot_id" = ? AND (locale = 'fr_FR' OR 'en_EN')) [["core_bot_id", 1]]

代码如下:

@filter = Filter.find_by_letter_id(@letter.id)
        $i = 1
        $a = 1
        $s = 1
        query = [{first_name: @filter.first_name}, {last_name: @filter.last_name}, {source: @filter.segment}, {gender: @filter.gender}, {timezone: @filter.timezone}, {locale: @filter.locale}, {created_at: [@filter.creation_date_start, @filter.creation_date_finish]}]
        query_chain = BotUser.where(core_bot_id: @bot.id)

          query.each do |hash|
            hash.each_pair do |key, value|
              if value.present? == true
                  if key.to_s == "timezone"
                    while  $i < value.size do
                      query_chain = query_chain.where("timezone = ?", value[$i].to_f)
                      $i += 1
                    end
                  elsif key.to_s == "locale"
                    while  $a < value.size do
                      puts $a.to_s
                      if $a == 1
                        query_chain = query_chain.where("locale = ?", value[$a])
                      else
                        query_chain = query_chain.or(query_chain.where("locale = ?", value[$a]))
                      end
                      $a += 1
                    end
                  elsif key.to_s == "gender"
                      query_chain = query_chain.where("gender = ?", value)
                  elsif key.to_s == "core_bot_id"
                      query_chain = query_chain.where("core_bot_id = ?", value)
                  elsif key.to_s == "created_at"
                    if value[0].present? == true and value[1].present? == true
                      query_chain = query_chain.where('created_at BETWEEN ? AND ?', value[0], value[1].end_of_day)
                    elsif value[0].present? == true
                      query_chain = query_chain.where('created_at > ?', value[0])
                    elsif value[1].present? == true
                      query_chain = query_chain.where('created_at < ?', value[1].end_of_day)
                    end
                  else
                    query_chain = query_chain.where("#{key} = ?", value)
                  end
                end
              end
          end

更新，尝试 Jaril 方法:

控制器:

private

    def filter_params
      params.fetch(:query, {}).permit(:first_name, :last_name, :timezone, :gender)
    end

    def set_nb_recipients
    @filter = Filter.find_by_letter_id(@letter.id)


filter_params = ActionController::Parameters.new({
 query: {
        core_bot_id: @bot.id,
        first_name:  @filter.first_name,
        last_name: @filter.last_name,
        source: @filter.segment,
        gender: @filter.gender,
        timezone: @filter.timezone,
        locale: @filter.locale,
        creation_date_start: @filter.creation_date_start,
        creation_date_finish: @filter.creation_date_finish
      }
    })
    query = FilterQuery.new(filter_params)

            query = FilterQuery.new(filter_params)
            @bot_users = query.execute || BotUser.none

            @nb_users = @bot_users.length
     end

app/models/filter_query.rb

class FilterQuery

  include ActiveModel::Model

  attr_accessor :first_name, :last_name, :timezone, :gender, :locale, :core_bot_id, :source, :creation_date_start, :creation_date_finish

  validates :gender, inclusion: { in: %w(male female) }

  def initialize(params)
    super(params)
  end

  def execute
    return false unless valid?
    @bot_users = BotUser.where(core_bot_id: core_bot_id)
    @bot_users = @bot_users.where('first_name LIKE ?', "#{first_name}%") if first_name.present?
    @bot_users = @bot_users.where('last_name LIKE ?', "#{last_name}%") if last_name.present?
    @bot_users = @bot_users.where(timezone: timezone) if timezone.present?
    @bot_users = @bot_users.where(timezone: locale) if locale.present?
    @bot_users = @bot_users.where(gender: gender) if gender.present?
    @bot_users = @bot_users.where(source: source) if source.present?
    @bot_users = @bot_users.where('created_at BETWEEN ? AND ?', creation_date_start, creation_date_finish) if creation_date_start.present? and creation_date_finish.present?
    @bot_users = @bot_users.where('created_at > ?', creation_date_start) if creation_date_start.present? and creation_date_finish.present? == false
    @bot_users = @bot_users.where('created_at < ?', creation_date_finish) if creation_date_start.present? == false and creation_date_finish.present?
    @bot_users
  end

end

不幸的是，这不会返回任何内容.我不确定 params 部分，你能帮我吗?我将数据存储在数据库中并从对象中获取参数.

Unfortunately, this does not return anything. I'm not sure about the params part, could you help me with that? I store the data in a database and get the params from the object.

推荐答案

我和 Jaryl 在一起.

I'm with Jaryl.

更少的if/elsif.您正在寻找的是 案例.并且由于您的一堆查询具有相似的结构，您可以将它们填充到 case 语句的 else 子句中.

Less if/elsif. What you're looking for is case. And since a bunch of your queries have similar structure, you can stuff those into the else clause of the case statement.

少 if x?== 真.如果 x 有一个问号，那么它已经返回了 true 或 false.你不必说，if true == true.就说，if x?.比如，if value[0].present?.根据您的具体要求，您可能也可以跳过 present? 部分.如果您只是想防范 nil 值，那么您可以只执行 if value[0].但是，正如engineermnky 在评论中指出的那样，如果您想防范空字符串、散列和数组，那么您需要坚持使用if value[0].present?.请记住，如果您不打算执行 else，您始终可以将 if 语句放在一行的末尾.喜欢:

Less if x? == true. If x has a question mark, then it's already returning a true or false. You don't have to say, if true == true. Just say, if x?. Like, if value[0].present?. Depending on your specific requirements, you may be able to skip the present? part, as well. If you're just trying to guard against nil values, then you could just do if value[0]. However, as engineersmnky points out in the comments, if you want to guard against empty strings, hashes, and arrays - then you'll need to stick with if value[0].present?. And remember, you can always stick your if statement at the end of a single line if you're not going to do an else. Like:

query_chain = query_chain.where('created_at > ?', value[0]) if value[0].present?

较少的类型转换(key.to_s).只需将键变量与另一个键进行比较.为什么要转换成字符串?

Less type conversion (key.to_s). Just compare the key variable to another key. Why convert it to a string?

减少循环.特别是那些迭代变量和值比较(while $i ) - 真恶心！这:

Less looping. Especially with those iteration variables and value comparisons (while $i < value.size) - yucky! This:

while  $i < value.size do
  query_chain = query_chain.where("timezone = ?", value[$i].to_f)
  $i += 1
end

不是惯用语.更好的是:

Is not idiomatic. Better would be:

value.each do |timezone|
  query_chain = query_chain.where("timezone = ?", timezone.to_f)
end

当然，您可以使该查询不那么冗长:

Of course, you could make that query a little less verbose:

value.each do |timezone|
  query_chain = query_chain.where(timezone: timezone.to_f)
end

但是，您在每个循环中所做的一切都是将时区转换为_f.那么，为什么不一次性完成并链接一个查询，例如:

But, all you're doing in that each loop is converting timezone to_f. So, why not do that in one shot and chain a single query, like:

timezones = value.map{|timezone| timezone.to_f}
query_chain = query_chain.where(timezone: timezones)    

当然，您可以保存自己的临时变量赋值，然后执行:

Of course, you could save yourself the temporary variable assignment and just do:

query_chain = query_chain.where(timezone: value.map{|timezone| timezone.to_f})

如果你不介意长(ish)行.

If you don't mind the long(ish) line.

我喜欢 Jaryl 的方法.但是，如果您想坚持当前的方法，它可能看起来像:

I like Jaryl's approach. If you want to stick with your current approach, though, it could look something like:

query.each do |hash|
  hash.each_pair do |key, value|
    if value
      case key
      when :timezone
        query_chain = query_chain.where(timezone: value.map{|timezone| timezone.to_f})
      when :created_at
        query_chain = query_chain.where('created_at > ?', value[0]) if value[0]
        query_chain = query_chain.where('created_at < ?', value[1].end_of_day) if value[1]
      else
        query_chain = query_chain.where(key => value)
      end
    end
  end
end

这是 Jaryl 方法的一个略有不同的实现...

Here's a slightly different implementation of the Jaryl approach...

class FilterQuery

  attr_accessor :first_name, 
                :last_name, 
                :timezone, 
                :gender, 
                :locale, 
                :core_bot_id, 
                :source, 
                :creation_date_start, 
                :creation_date_finish

    def initialize(params)
      params.each{|k,v| send("#{k}=",v)}
    end

    def execute
      return false unless valid?
      @bot_users = BotUser.where(core_bot_id: core_bot_id)
      [:first_name, :last_name].each do |var_sym|
        val = send(var_sym)
        @bot_users = @bot_users.where("#{var_sym} LIKE ?", "#{val}%") if val.present?
      end
      [:timezone, :locale, :gender, :source].each do |var_sym|
        val = send(var_sym)
        @bot_users = @bot_users.where(var_sym => val) if val.present?
      end
      @bot_users = @bot_users.where('created_at > ?', creation_date_start) if creation_date_start.present?
      @bot_users = @bot_users.where('created_at < ?', creation_date_finish) if creation_date_finish.present?
      @bot_users
    end

  private

    def valid?
      %w(male female).include? gender
    end

end

这篇关于Rails where 查询链与或用于数组输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！