0

I have a database with Product and product has a name attribute which I want to search. Im using a scope to do that:

scope name_contains, lambda{|q| where("name LIKE ?", "%#{q}%")}

which works fine when I search for "Awesome Product" and the product is called "A pretty Awesome Product" but not when I search for "product awesome" because the words are in the wrong direction. What I tried next was splitting the query by space and call the scope for every word:

def self.search(q)
  q.split(" ").map(&:strip).select{|x| !x.empty? }.each_with_index do |word,index|
    if index == 0
      data = Product.name_contains(word)
    else
      data = data.name_contains(word)
    end
  end
  data
end

Which works but it hits the database for every word. But I know rails can handle this better because this:

Product.name_contains("awesome").name_contains("product")

hits the database only once. This is what I want but can't realize when calling the name_contains method in an iteration.

davidb
  • 8,884
  • 4
  • 36
  • 72
  • Which DB you are using ? – Arup Rakshit Feb 05 '15 at 12:59
  • Full-text search functions seem more appropriate IMO. – Stefan Feb 05 '15 at 12:59
  • Ahh! then you missed something interesting - http://www.postgresql.org/docs/9.0/interactive/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP :-) – Arup Rakshit Feb 05 '15 at 13:02
  • @Stefan: Full Text search might be a easy solution for this. I know that but I'm sure there must be a nice solution using scopes and I'm interrested in such solution because it whould be "beautiful" – davidb Feb 05 '15 at 13:03
  • Is it _nice_ http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html ? – Arup Rakshit Feb 05 '15 at 13:04
  • @davidb Last one from MySql.. :-D – Arup Rakshit Feb 05 '15 at 13:05
  • Let me write `SELECT * FROM quotes_table WHERE MATCH (quote) AGAINST ('"mangé"' IN BOOLEAN MODE)` in Rails as `QuoteTable.where("MATCH (?) AGAINST ( (?) IN BOOLEAN MODE"), quote, "mangé")`.. I think it will work. Although I didn't try, as I don't have DB. – Arup Rakshit Feb 05 '15 at 13:09
  • FULLTEXT searches aren't working with MySQL when you use INNODB it only works with MyISAM. – davidb Feb 05 '15 at 13:26

1 Answers1

1

something like

words.inject(Product.all) {|scope, word| scope.name_contains(word)}

Should do the trick. Before rails 4 you'll want Product.scoped instead of Product.all

Do note that this will require a full table scan (unless you have other conditions you'll be applying) - mysql won't be able to use any indexes for this query

Frederick Cheung
  • 83,189
  • 8
  • 152
  • 174
  • This whould work technically but it wont because there I have about 200.000 records that's to much for every search request... – davidb Feb 05 '15 at 13:27
  • This is no different to `Product.name_contains("awesome").name_contains("product")`. If you have too many products for that then you need a full text search solution – Frederick Cheung Feb 05 '15 at 13:37
  • Ohh your right it works just like I wanted it to work. It generates a sql query and only hits the database one. I thought it whould query all records first but it doesn't – davidb Feb 05 '15 at 13:41
  • @davidb: `all` just returns a scope in rails 4.0 and above – Frederick Cheung Feb 05 '15 at 14:14