Autocomplete the city/state/country name when user types it in its own locale, i.e. User types 'Nowy Jo', query returns 'New York'

apartment_location_autocomplete_controller.rb

module Remotes
  class ApartmentLocationAutocompleteController < ApplicationController
    def index
      # https://github.com/shioyama/mobility/wiki/KeyValue-Backend#querying
      # https://github.com/shioyama/mobility#querying

      sql = ["
        #{select_sql('apartments')} \
        #{join_translation_sql('Apartment', 'state', 'string')} \
        #{join_translation_sql('Apartment', 'country', 'string')} \
        WHERE #{mobility_where_sql('Apartment', 'state', 'string')} \
        OR #{mobility_where_sql('Apartment', 'country', 'string')} \
        OR #{where_sql('city_name')} \
        OR #{where_sql('district')} \
        OR #{where_sql('neighborhood')}
      ",
      query: "%#{params[:query]}%"]

      # watch the performance with large data
      # two big queries are being ececuted
      apartments = Apartment.i18n.where(
        id: Apartment.find_by_sql(sql).map(&:id)
      ).eager_load(:string_translations)

      data = []
      apartments.each do |a|
        data << { name: a.city_name } if normalized_compare_start_with?(a.city_name, params[:query])
        data << { name: a.send("state_#{I18n.locale}") } if normalized_compare_start_with?(a.send("state_#{I18n.locale}"), params[:query])
        data << { name: a.send("country_#{I18n.locale}") } if normalized_compare_start_with?(a.send("country_#{I18n.locale}"), params[:query])
      end

      if data.count <= 5
        extra_data = []
        apartments.each do |a|
          extra_data << { name: a.city_name } if normalized_compare_include?(a.city_name, params[:query])
          extra_data << { name: a.send("state_#{I18n.locale}") } if normalized_compare_include?(a.send("state_#{I18n.locale}"), params[:query])
          extra_data << { name: a.send("country_#{I18n.locale}") } if normalized_compare_include?(a.send("country_#{I18n.locale}"), params[:query])
        end

        data << extra_data.sort_by { |h| h[:name] }
        data.flatten!
      end

      render json: data.uniq
    end

    private

    def normalized_compare_start_with?(name, query)
      I18n.transliterate(name.downcase).start_with?(I18n.transliterate(query.downcase))
    end

    def normalized_compare_include?(name, query)
      I18n.transliterate(name.downcase).include?(I18n.transliterate(query.downcase))
    end

    def select_sql(table_name)
      "SELECT \"#{table_name}\".* FROM \"#{table_name}\""
    end

    def join_translation_sql(klass, attribute, column_type)
      "
        INNER JOIN \"mobility_#{column_type}_translations\" \"#{klass}_#{attribute}_#{I18n.locale}_#{column_type}_translations\"
          ON \"#{klass}_#{attribute}_#{I18n.locale}_#{column_type}_translations\".\"key\" = '#{attribute}'
          AND \"#{klass}_#{attribute}_#{I18n.locale}_#{column_type}_translations\".\"locale\" = '#{I18n.locale}'
          AND \"#{klass}_#{attribute}_#{I18n.locale}_#{column_type}_translations\".\"translatable_type\" = '#{klass}'
          AND \"#{klass}_#{attribute}_#{I18n.locale}_#{column_type}_translations\".\"translatable_id\" = \"#{klass.downcase.pluralize}\".\"id\"
      "
    end

    def mobility_where_sql(klass, attribute, column_type)
      "\"#{klass}_#{attribute}_#{I18n.locale}_#{column_type}_translations\".\"value\" ILIKE :query"
    end

    def where_sql(attribute)
      "\"#{attribute}\" ILIKE :query"
    end
  end
end

Please ignore the fact that this logic should not be placed in a controller action.

🤔 The problem:
We have two locales: en 🇬🇧 and pl 🇵🇱 , the table apartments and the city of Warsaw. SO we need dynamic translations to handle names in both locales, i.e. Warsaw|Warszawa, Washington|Washyngton

When either a Polish or English user wants to query this table for autocomplete input, I expect Polish users to type "Warsz...", while for the English users, I expect to type "Wars..." and both params should return the apartments in the city of Warsaw.
We don't want to store the name of the city of Warsaw in both locales (Warszawa and Warsaw) in the table apartments. This is where Mobility Gem comes to the rescue.
The extra caveat is that the user can type into this input city name, state name, or country name, so we need to handle this too, besides the localized name of any of these three attributes.

🔢 The data:
The data in the apartments table is stored with only English names, i.e. Warsaw, Masovia, Poland
In tables generated by Mobility gem, we store localized names, for example for Polish locale: Warszawa, mazowieckie, Polska

💡 The solution:
This logic is responsible for querying the data independent from the localized input name of the city, state or country.

Example 1:

• 🇵🇱 Polish speaking user inputs: "Wa"
• the query returns the apartments in "Warsaw" and "Washington" (localized at the output to "Warszawa" and "Waszyngton" by another logic)

Example 2:

• 🇵🇱 Polish speaking user inputs: "Warsz"
• the query returns the record "Warsaw" (localized at the output to "Warszawa" by another logic)

Example 3:

• 🇵🇱 Polish-speaking user inputs: "Wasz"
• the query returns the apartments in "Washington" (localized at the output to "Waszyngton" by another logic)

Example 4:

• 🇬🇧 🇺🇸 English speaking user inputs: "Wars"
• the query returns the record "Warsaw"

Example 5:

• 🇬🇧 🇺🇸 English speaking user inputs: "Cra"
• the query returns the record "Cracow" and "Crawfordsville"