Search with accented characters

Question

I have an app that stores titles and lyrics. Some songs are in spanish and contain áccénts. I'm trying to write a search method where a user can enter a word without accents. For example, let's say the title has the word "papá" in it. If the user types in "papa", it should still find that song with that word in the title.

This is what I have so far. It handles wildcards and searches through 3 columns.

class Song < ApplicationRecord
  def self.search(query)
    return all unless query.present?

    # for case insensitive search
    like = Rails.env.production? ? 'ILIKE' : 'LIKE'

    # for wildcards
    q = ["%#{query}%"] * 3

    # columns that I will search through song.number, song.title, song.lyrics
    search_phrase = "number #{like} ? OR title #{like} ? OR lyrics #{like} ?"

    where([search_phrase] + q)
  end
end

@kurenn I will in production – Cruz Nunez Jan 30 '17 at 17:25 — Cruz Nunez, Jan 30 '17 at 17:25

score 2 · Answer 1 · answered Jan 30 '17 at 04:13

2

You can use collation:

search_phrase = "number #{like} ? OR title COLLATE Latin1_general_CI_AI #{like} ? OR lyrics #{like} ?"

The CI stands for case-insensitive and the AI stands for accent-insensitive.

answered Jan 30 '17 at 04:13

Shannon

2,988
10
21

If your MySQL server doesn't support the collations with `_ai` endings, try `_bin`. For example `utf8_bin`, `latin1_bin`, etc. – Andrew Aug 26 '19 at 21:55

score 1 · Answer 2 · answered Jan 30 '17 at 17:34

The best way to achieve this using postgresql is to add the unaccent extension:

class AddUnaccentExtension < ActiveRecord::Migration
  def change
    enable_extension "unaccent"
  end
end

And then when you want to query for results just:

search_phrase = "unaccent(number) #{like} unaccent(?) OR unaccent(title) #{like} unaccent(?) OR unaccent(lyrics) #{like} unaccent(?)"

Have in mind this will only work on postgresql, I recommend you to use as the development database to, to prevent weird stuff from happening

score 0 · Accepted Answer · edited May 23 '17 at 11:53

If you can't get the COLLATE Latin1_general_CI_AI or pg setup to work, try this. Duplicate columns, one accented, one not. Credit goes to this and this answer.

class Song < ApplicationRecord
  before_save :i18n

  def self.search(query)
    return all unless query.present?

    like = Rails.env.production? ? 'ILIKE' : 'LIKE'

    q = ["%#{query}%"] * 3

    search_phrase = "number #{like} ? OR ai_title #{like} ? OR ai_lyrics #{like} ?"

    where([search_phrase] + q)
  end

  def i18n
    self.ai_title = I18n.transliterate title
    self.ai_lyrics = I18n.transliterate lyrics
  end
end

With my migration looking like:

class CreateSongs < ActiveRecord::Migration[5.0]
  def change
    create_table :songs do |t|
      ...
      t.string :title
      t.string :ai_title, index: true
      t.text :lyrics
      t.text :ai_lyrics, index: true
      ...
    end
  end
end

It works for many database setups. I find this real useful.

Search with accented characters

3 Answers3