CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordrails-activerecord
Tolga
Tolga

Reputation: 1357

ActiveRecord find all records including spesific foreign word (case insensitive)

I would like to write an ActiveRecord query to fetch all records which include specific foreign language words (case insensitive) in the description column of records table.

I think I can use mb_chars.downcase which converts international chars to lower case successfully.

> "ÖİÜŞĞ".mb_chars.downcase
=> "öiüşğ"

However, when I try to use mb_chars.downcase in ActiveRecord query I receive the following error:

def self.targetwords
  target_keywords_array = ['%ağaç%','%üzüm%','%çocuk%']
  Record.where('description.mb_chars.downcase ILIKE ANY ( array[?] )', target_keywords_array)
end

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "mb_chars"

I will appreciate if you can guide me how to solve this problem.

Upvotes: 1

Views: 49

Answers (1)

mu is too short
mu is too short

Reputation: 434975

You're trying too hard, you should let the database do the work. PostgreSQL knows how to work with those characters in a case-insensitive fashion so you should just let ilike deal with it inside the database. For example:

psql> select 'ÖİÜŞĞ' ilike '%öiüşğ%';
 ?column? 
----------
 t
(1 row)

You could say simply this:

def self.targetwords
  target_keywords_array = ['%ağaç%','%üzüm%','%çocuk%']
  Record.where('description ILIKE ANY ( array[?] )', target_keywords_array)
end

or even:

def self.targetwords
  Record.where('description ilike any(array[?])', %w[ağaç üzüm çocuk].map { |w| "%#{w}%" })
end

or:

def self.targetwords
  words   = %w[ağaç üzüm çocuk]
  likeify = ->(w) { "%#{w}%" }
  Record.where('description ilike any(array[?])', words.map(&likeify))
end

As far as why

Record.where('description.mb_chars.downcase ILIKE ANY ( array[?] )', ...)

doesn't work, the problem is that description.mb_chars.downcase is a bit of Ruby code but the string you pass to where is a piece of SQL. SQL doesn't know anything about Ruby methods or using . to call methods.

Upvotes: 3

Related Questions