0

I'd like to search the whole string in join columns. I have table products with columns: design, color, code and in index page I show them in one place using model method:

def name
 [design.name, color, code_name(code)].join(' ').to_s
end

I would like to search in this join column. For example: Join column is: "Strips Black Plain" And when if I will type in search field "Strips Bl" I want to get all matching srings. For instance: Strips Black Plain2, Strips Blue Plain, etc.

When I had search in one column I used model method:

 def self.search(letter)
    if letter
      where("colorLIKE ?", "%#{letter}%").order(:created_at)
    else
      all
  end

I know I can search at the same time in several columns but separately using operator ||. But how search in this join column like in one column?

Patrishio
  • 1
  • 1
  • I think you should look at this post http://stackoverflow.com/questions/3947273/rails-3-using-like-to-search-a-combined-2-columns and this post http://stackoverflow.com/questions/3866391/rails-gem-rails3-jquery-autocomplete-how-do-i-query-multiple-fields/3899006#3899006 – Moustafa Sallam Jul 18 '16 at 11:42

2 Answers2

0

I would change my search query to be like this one:

def search(keyword)
  where("LOWER (design) LIKE LOWER(:s) OR LOWER(color) LIKE LOWER(:s) OR LOWER(code) LIKE LOWER(:s) or concat(LOWER(design), ' ', LOWER(color)) LIKE LOWER(:s)", {s: "%#{keyword}%"})
end

So the main idea to use the CONCAT on the database level to search one two columns together. More about concatenate

Moustafa Sallam
  • 1,110
  • 1
  • 9
  • 17
  • Concat is exactly what I looking for! Thank you. Is any chance to use concat if one of the searching columns will be column from diffrent table? In my case column: design.name is from different table: design. And design belongs_to product. – Patrishio Jul 18 '16 at 14:10
0

SOLVED Join to combine 2 table. Concat to search in mutlitple columns.

joins(:design).where("LOWER(designs.name) LIKE LOWER(:s) OR LOWER(color) LIKE LOWER(:s) OR LOWER(code) LIKE LOWER(:s) or concat(LOWER(designs.name), ' ', LOWER(color), ' ', LOWER(code)) LIKE LOWER(:s)", {s: "%#{letter}%"})
Patrishio
  • 1
  • 1