使用postgreSQL 13和Rails 6+搜索多个表

我提供了很多上下文来为这个问题搭建舞台。我想解决的是使用结构化数据对多个数据库表进行快速准确的模糊搜索，而不是全文文档搜索。

我正在使用postgreSQL 13.4+和Rails 6+，如果它的关系。

我有几个表的相当结构化的数据:

class Contact
attribute :id
attribute :first_name
attribute :last_name
attribute :email
attribute :phone
end
class Organization
attribute :name
attribute :license_number
end
...several other tables...

我正试图实现一个快速和准确的模糊搜索，这样我就可以一次搜索所有这些表(Rails模型)。

目前，我有一个使用ILIKE的单独搜索查询，它连接了我想要对每个模型进行实时搜索的列:

# contact.rb
scope :search -> (q) { where("concat_ws(' ', first_name, last_name, email, phone) ILIKE :q", q: "%#{q}%") 
# organization.rb
scope :search -> (q) { where("concat_ws(' ', name, license_number) ILIKE :q", q: "%#{q}%") }

在我的搜索控制器中，我分别查询这些表，并显示每个模型的前3个结果。

@contacts = Contact.search(params[:q]).limit(3)
@organizations = Organization.search(params[:q]).limit(3)

这个工作，但相当慢，不像我想要的那样准确。

当前方法的问题:

缓慢(相对而言)，只有数千条记录。
不准确，因为ILIKE必须在字符串的某个地方有一个精确的匹配，我想实现模糊搜索(即，与ILIKE， "smth"would not match "smith")。
不加权;我想将contacts.last_name列的权重置于organizations.name列之上，因为联系人表通常是优先级更高的搜索项。

我的解决方案我的理论解决方案是创建一个search_entries多态表，该表对每个contact,organization等都有单独的记录，我想要搜索，然后这个search_entries表可以被索引以便快速检索。

class SearchEntry
attribute :data
belongs_to :searchable, polymorphic: true
# Store data as all lowercase to optimize search (avoid lower method in PG)
def data=(text)
self[:data] = text.lowercase
end
end

然而，我卡住的是如何构建这个表，以便它可以被索引和快速搜索。

contact = Contact.first
SearchEntry.create(searchable: contact, data: "#{contact.first_name} #{contact.last_name} #{contact.email} #{contact.phone}")
organization = Organization.first
SearchEntry.create(searchable: organization, data: "#{organization.name} #{organization.license_number}")

这使我能够做如下事情:

SearchEntry.where("data LIKE :q", q: "%#{q}%")

或者甚至像使用PG的similarity()函数的fuzzysearch:

SearchEntry.connection.execute("SELECT * FROM search_entries ORDER BY SIMILARITY(data, '#{q}') LIMIT 10")

我相信我可以在这个data字段上使用pg_trgm和GIN索引来优化搜索(不是100%在那…)。

这将我的搜索简化为单个表上的单个查询，但它仍然不允许我做加权列搜索(即，contacts.last_name比organizations.name更重要)。

<标题>

这种方法是否使我能够索引数据，以便我可以非常快速地进行模糊搜索?(我很快就知道了)是主观的，所以我的意思是有效地使用PG以尽快获得结果)。
我是否能够使用GIN索引与pg_trgm三格相结合来索引此数据以进行快速模糊搜索?
我如何在这样的方法中实现比其他值更高的权重?

一个可能的解决方案是创建一个物化视图，该视图由来自两个(或多个)表的数据联合组成。看这个简单的例子:

CREATE MATERIALIZED VIEW searchables AS
SELECT
resource_id,
resource_type,
name,
weight
FROM 
SELECT 
id as resource_id,
'Contact' as resource_type
concat_ws(' ', first_name, last_name) AS name,
1 AS weight
FROM contacts
UNION
SELECT 
id as resource_id,
'Organization' as resource_type
name
2 AS weight 
FROM organizations

class Searchable < ApplicationRecord
belongs_to :resource, polymorphic: true
def readonly?
true
end
# Search contacts and organziations with a higher weight on contacts
def self.search(name)
where(arel_table[:name].matches(name)).order(weight: :desc)
end
end

由于物化视图存储在类似表的结构中，因此您可以像使用普通表一样应用索引:

CREATE INDEX searchables_name_trgm ON name USING gist (searchables gist_trgm_ops);

对于ActiveRecord，它的行为就像一个普通的表。

当然，这里的复杂性将随着您想要搜索的列的数量而增长，最终结果可能在功能上不给人留下深刻印象，而在复杂性上与数千小时后的现成解决方案相比。

风景宝石可以用来简化创建物化视图的迁移。

相关内容

最新更新

热门标签：