В одном из последних своих проектов, где использовалась большая база контактов, у каждого контакта имелось несколько телефонов, понадобилось искать по номеру телефона. Недолго думая решил сделать это через встроенный ILIKE, но поиск почти ничего не находил, потому что в таблице с номерами телефонов хранились номера в совершенном разных форматов, например, +7(903)555-12-11, +46 8 52506000 и т.д. Поэтому нужно было придумать что-то более радикальное и в то же время не очень медленное.

Идея номер 2 заключалась в том, чтобы для каждого телефонного номера в этой же таблице сохранять номер телефона без специфических символов, т.е. 79035551211 и 46852506000 соответственно. Но идея не нравилась тем, что необходимо было почти дублировать колонку, оставил этот план про запас, на случай, если ничего более путного не придумаю.

Идея номер 3 заключается в использовании встроенных функций замены при поиске, например regexp_replace, которая позволяет заменять строки по регекспу.

SELECT number FROM phones WHERE regexp_replace(number, '\\d', '', 'g') ILIKE '%313%'; – найдет все номера, в которых встречаются подряд идущие цифры 313 (первый параметр это где ищем, второй – сам регексп \d – только цифры, третий – меняем на пустую строку и последний модификатор для регулярного выражения, который указывает что поиск нужно вести до победного, а не останавливается на первом вхождении). Но опять же есть небольшая проблема, если посмотреть EXPLAIN, то становится понятно в чем она.

Для этого запускаем EXPLAIN ANALYZE SELECT number FROM phones WHERE regexp_replace(number, '\\d', '', 'g') ILIKE '%313%'; в результате видим примерно следующее.

Что как бы намекает нам, что это очень долго, потому что поиск идет строка за строкой, просматривая каждую строку таблицы. Значит, нам нужен индекс, комплексный индекс.

CREATE INDEX idx_phones_search_on_number ON phones USING gist(regexp_replace(number, '\\d', '', 'g') gist_trgm_ops);

Проверяем, запускаем такой же запрос и получаем следующую картину.

Мы видим, что сейчас начал использоваться индекс и скорость выполнения запроса возросла в 6 с небольшим раз. Магия индексов воочию.

Но давайте пойдем чуть дальше и допустим, что пользователь сам вводит номер телефона, по которому собирается найти контакт. Он может это сделать как нам удобно 79035551211 или же как нам вообще не удобно, +7(903)555-12-11 и более того, он ничего не найдет. Значит нужно и в запросе от пользователя удалять все нецифровые символы. Например, так:

'+7(903)555-12-11'.gsub(/\D/, ''), но это добавляет некоторую головную боль, т.к. и в ruby, и в sql придется выполнять одно и то же действие, но на разных языках, что не есть хорошо.

Помогут решить нам эту проблему postgresql функции. Одна будет удалять все нецифровые символы, другая будет оставлять только цифры и знак %, для задания маски поиска. Для этого создадим миграцию для добавления двух функций и заодно добавим индекс. Получится следующее:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

class AddFunctionsAndIndexForSearchByPhone < ActiveRecord::Migration
  def up
    ActiveRecord::Base.connection.execute %q{
      CREATE OR REPLACE FUNCTION onlynum(n text) RETURNS text AS
      $func$
      SELECT regexp_replace($1, '\\D', '', 'g');
      $func$ LANGUAGE sql IMMUTABLE;

      CREATE OR REPLACE FUNCTION onlynums(n text) RETURNS text AS
      $func$
      SELECT regexp_replace($1, '[^\d%]', '', 'g');
      $func$ LANGUAGE sql IMMUTABLE;

      CREATE INDEX idx_phones_search_on_number ON phones USING gist(onlynum(number) gist_trgm_ops);
    }
  end

  def down
    ActiveRecord::Base.connection.execute %Q{
      DROP INDEX idx_phones_search_on_number;

      DROP FUNCTION onlynum(text);
      DROP FUNCTION onlynums(text);
    }
  end
end

Запустим миграцию rake db:migrate и добавим где-нибудь в модель, пусть это будет Phone метод .search примерно следующего содержания:

1
2
3
4
5

class Phone < ActiveRecord::Base
  def self.search term
    where 'onlynum(phones.number) LIKE onlynums(:search)', search: "%#{term}%"
  end
end

Это очень упрощенный метод, но он работает, теперь для поиска телефона, необходимо вызвать где-нибудь в коде наш метод: Phone.search '313' и вы получите список строк, телефоны которых содержат заданную строку.

Надеюсь, данный пост поможет вам. Все мысли, ошибки, неточности можете оставлять в комментариях, с удовольствием их почитаю :) Мир, братья и сестры.