ruby-on-rails - 带有或用于数组输入的查询链的 Rails

Question

我想创建一个查询链，以便根据特定过滤器找到我的机器人用户。一些过滤器可以有多个值。例如，我的“语言环境”过滤器可以有多个值(例如 fr_FR、en_US)。

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

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

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]]

代码如下:

@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 方法:

Controller :

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

不幸的是，这不会返回任何东西。我不确定参数部分，你能帮我吗？我将数据存储在数据库中并从对象中获取参数。

Answer 1

我和 Jaryl 在一起。

少if/elsif .您要找的是case .由于您的许多查询具有相似的结构，因此您可以将它们填充到 else 中。 case 的条款声明。

少if x? == true .如果x有一个问号，那么它已经返回一个 true或 false .你不必说，if true == true .就说，if x? .喜欢，if value[0].present? .根据您的具体要求，您可能可以跳过 present?部分，以及。如果你只是想防范 nil值，那么你可以做 if value[0] .然而，正如 engineersmnky 在评论中指出的那样，如果你想防止空字符串、散列和数组——那么你需要坚持使用 if value[0].present?。 .请记住，您可以随时粘贴 if如果您不打算执行 else，则在单行末尾声明.喜欢:

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

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

减少循环。特别是对于那些迭代变量和值比较 ( while $i < value.size ) - 讨厌!这:

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

不是惯用语。更好的做法是:

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

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

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

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

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

当然，您可以省去临时变量赋值，然后执行以下操作:

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

如果您不介意排长队的话。

我喜欢 Jaryl 的方法。不过，如果您想坚持使用当前的方法，它可能类似于:

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 方法的一个稍微不同的实现...

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