gistfile1.txt

class Search < ApplicationRecord
  def self.search_posts(keywords)
    posts = Post.all
    posts = posts.where(["title LIKE ?", "%#{keywords}%"]) if keywords.present?
    return posts

  end
end

1. Important: wrap keywords with sanitize_sql_like, so a % in the query is interpreted literally rather than triggering a more expensive query.

2. Consider this if you want to have search terms act as keywords (all match anywhere) rather than requiring an exact phrase match, e.g. searching "mice blind" matches a post titled "Three blind mice":

   keywords.to_s.split.each do |keyword|
     posts = posts.where(["title LIKE ?", "%#{sanitize_sql_like(keyword)}%"])
   end

   (If you know keywords will never be nil, you can remove the .to_s in the chain.)

3. The LIKE approach will get slower as the number of posts gets larger. Maybe create 1000 fake posts containing a typical length titles, and see what the performance is like. This way you'll know what to expect. The Faker gem could be helpful for generating a variety of fake titles via Rails console.

4. If you're using PostgreSQL instead of MySQL, use case-insensitive ILIKE instead of LIKE.

Got it...

…

On Thu, May 9, 2019 at 11:06 AM Edward Anderson ***@***.***> wrote: 1. Important: wrap keywords with sanitize_sql_like <https://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html#method-i-sanitize_sql_like>, so a % in the query is interpreted literally rather than triggering a more expensive query. 2. Consider this if you want to have search terms act as keywords (all match anywhere) rather than requiring an exact phrase match, e.g. searching "three mice" matches a post containing "three blind mice": keywords.to_s.split.each do |keyword| posts = posts.where(["title LIKE ?", "%#{sanitize_sql_like(keyword)}%"])end (If you know keywords will never be nil, you can remove the .to_s in the chain.) 3. The LIKE approach will get slower as the number of posts gets larger. Maybe create 1000 fake posts containing a typical length titles, and see what the performance is like. This way you'll know what to expect. The Faker <https://github.com/stympy/faker> could be helpful for generating a variety of fake titles via Rails console. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://gist.github.com/84151271e9b14ceed1997b2fb151a26c#gistcomment-2913061>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAO4XUTFDOZD2LPTNNBFF2DPUQ4VVANCNFSM4HL3MDHQ> .

3. The LIKE approach will get slower as the number of posts gets larger.

If you do #2, then performance may or may not get significantly worse when increasing the number of search terms. (Nothing is stopping a user from pasting in a paragraph, hoping it will match the body…) I'd test with that too. This could affect multiple LIKE searches but should be fine if you switch to fulltext search later.