使用OR和AND搜索查询多列以应用过滤器 [英] Search query multiple column with OR and AND to apply filter

问题描述

我正在开发应用程序。我试图植入搜索系统。方法很简单。在家里，我在3列名称或 nomdep或 nomregion上搜索查询。此后，我将使用AND运算符根据参数进行过滤。

I working on an app. I trying to implant a search system. Method is simple. On home i search a query on 3 column "name" OR "nomdep" OR "nomregion". After this, I wnat to filter by params with AND operator.

如果我仅使用有效的一列（例如 NAME）进行搜索，则可以应用过滤器。但是，如果我想植入一个以上的对象，或者我不能应用过滤器。我认为OR运算符是问题所在。但是可以肯定的是我不知道要解决这个问题...

If I implant search with only one column for example "NAME" that's work, i can apply filters. But if i want to implant this with more than one OR I cant apply filter. I think OR operator is the problem. But for sure i have no idea to solve this...

你能帮我吗？谢谢

camping.rb

camping.rb

has_many :caracteristiquetests, :foreign_key => :camping_id
has_many :situations, :foreign_key => :camping_id

    def self.searchi(query, handicap, animaux, television, plage, etang, lac)
       return scoped unless query.present?
       left_outer_joins(:caracteristiquetests, :situations).where(['nomdep LIKE ? OR name LIKE ? OR nomregion LIKE ? AND handicap LIKE ? AND animaux LIKE ? AND television LIKE ? AND plage LIKE ? AND etang LIKE ? AND lac LIKE ?', "%#{query}%", "%#{query}%", "%#{query}%", "%#{handicap}%", "%#{animaux}%", "%#{television}%", "%#{plage}%", "%#{etang}%", "%#{lac}%"])
    end

camping_controller.rb

camping_controller.rb

      def resultnohome
        if params[:query].blank?
          redirect_to action: :index and return
        else
          @campings = Camping.searchi(params[:query], params[:handicap], params[:animaux], params[:television], params[:plage], params[:etang], params[:lac])
        end
end

caracteristiquetest.rb

caracteristiquetest.rb

belongs_to :camping

situation.rb

situation.rb

belongs_to :camping

编辑

我编辑了模型以添加（ ）

EDIT

I edited my model to add "()"

camping.rb

camping.rb

def self.searchi(query, handicap, animaux, television, plage, etang, lac)
   return scoped unless query.present?
   left_outer_joins(:caracteristiquetests, :situations).where(['(nomdep LIKE ? OR name LIKE ? OR nomregion LIKE ?) AND handicap LIKE ? AND animaux LIKE ? AND television LIKE ? AND plage LIKE ? AND etang LIKE ? AND lac LIKE ?', "%#{query}%", "%#{query}%", "%#{query}%", "%#{handicap}%", "%#{animaux}%", "%#{television}%", "%#{plage}%", "%#{etang}%", "%#{lac}%"])
end

很不幸，我得到了意外的结果：仅显示一个结果，第一个匹配项是短路评估。知道为什么吗？

Unfornutualy, I m getting unexpected results : only one result is display, the first match like "Short circuit evaluation". Any ideas why ?

推荐答案

只需在或条件周围使用括号， 或条件的结果将与和条件相结合。

Just use parentheses around the or conditions, the result of the or conditions will then be combined with the and conditions.

left_outer_joins(:caracteristiquetests, :situations).where(['(nomdep LIKE ? OR name LIKE ? OR nomregion LIKE ?) AND handicap LIKE ? AND animaux LIKE ? AND television LIKE ? AND plage LIKE ? AND etang LIKE ? AND lac LIKE ?', "%#{query}%", "%#{query}%", "%#{query}%", "%#{handicap}%", "%#{animaux}%", "%#{television}%", "%#{plage}%", "%#{etang}%", "%#{lac}%"])

或者，您可以将其拆分为单独的 语句，当您检索结果时，这些语句将组合在一起以进行一个SQL调用。这将更易于阅读和维护，并且不会在未提供值的情况下进行任何调用。

Alternatively, you can split it up into separate where statements, which will combine to make one SQL call when you retrieve the results. This would be easier to read and maintain, and doesn't do any calls where a value wasn't provided.

result = left_outer_joins(:caracteristiquetests, :situations).where('nomdep LIKE ? OR name LIKE ? OR nomregion LIKE ?', "%#{query}%", "%#{query}%", "%#{query}%")
result = result.where('handicap LIKE ?', "%#{handicap}%") if handicap
result = result.where('animaux LIKE ?', "%#{animaux}%") if animaux
result = result.where('television LIKE ?', "%#{television}%") if television
result = result.where('plage LIKE ?', "%#{plage}%") if plage
result = result.where('etang LIKE ?', "%#{etang}%") if etange
result = result.where('lac LIKE ?', "%#{lac}%") if lac
return result

这篇关于使用OR和AND搜索查询多列以应用过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！