Reputation: 1357
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
Reputation: 434985
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