CODEWITHSUNDEEP
ruby-on-railspostgresql
Ali Ove
Ali Ove

Reputation: 79

Rails checking for multiple words in text as a query

Currently, I am trying to check the text in reviews stored on my database using the following loop.

def bad_words_check 
    bad_words = ["covid", "corona", "coronavirus", "pandemic", "quarantine", "lockdown", "virus"]
    bad_words.each do |word|
    Comment.where("comment_text ~* ?", word).update(flag_covid: true)
    end
  end

However, this seems to run as an infinite loop. It was much faster and more efficient database-wise to run each word separately in a single line query in my rails console.

Comment.where("comment_text ~* ?", "covid").update(flag_covid: true)

Surely there has to be a better way?

Upvotes: 0

Views: 325

Answers (2)

Bouaik Lhoussaine
Bouaik Lhoussaine

Reputation: 584

If you just want to check if the whole comment is a bad word:

Comment.where(comment_text: bad_words)

This is the same as:

Comment.where("comment_text ILIKE ANY (ARRAY[?])", bad_words) # for Postegres
Comment.where("comment_text LIKE in ?", bad_words) # for MySql

However, if the word can be included in the comment, you should loop and check every comment:

bad_words.each |bad_word| do
   Comment.where("comment_text LIKE in ?", '%#{bad_word}%')
end

Upvotes: 4

eux
eux

Reputation: 3282

You could use ILIKE and ANY to do this:

bad_words = bad_words.map { |word| "%#{word}%" }

Comment.where("comment_text ILIKE ANY (ARRAY[?])", bad_words)
       .update(flag_covid: true)

Upvotes: 2

Related Questions