How do I perform a search with postgres ignoring diacritics?

[leocabeza]

Right now I've stumbled upon a bug in my code, and it relates to text having accent in the database, and when the user search for a text without them, they don't match:

For instance:

const paginatedOrders = await Order.query()
      .select('*')
      .orderBy('created_at', 'desc')
      .preload('products')
      .preload('address', (addressQuery) => {
        addressQuery.preload('client', (clientQuery) => {
          if (clientSearchTerm) {
            // This won't work if clientSearchTerm is "Maria", because the database data contains "María"
            clientQuery.whereILike('name', `%${clientSearchTerm}%`)
          }
        })
      })
      .paginate(page, limit)

[leocabeza]

I solved this by changing the query and installing the unaccent extension:

// migration

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class extends BaseSchema {
  protected tableName = 'clients'

  public async up() {
    this.defer(async (db) => {
      await db.rawQuery('CREATE EXTENSION unaccent;')
    })
  }

  public async down() {
    this.defer(async (db) => {
      await db.rawQuery('DROP EXTENSION IF EXISTS unaccent')
    })
  }
}

// controller

const paginatedOrders = await Order.query()
      .select('*')
      .orderBy('created_at', 'desc')
      .preload('products')
      .preload('address', (addressQuery) => {
        addressQuery.preload('client', (clientQuery) => {

          if (clientSearchTerm) {
            clientQuery.andWhereRaw(`unaccent(name) ILIKE unaccent(?)`, [`%${clientSearchTerm}%`])
          }
        })
      })