如果这是一个愚蠢的问题,请谅解我。
我在迁移中有以下代码:
DB::statement("
CREATE MATERIALIZED VIEW searchable_stores AS
SELECT
chain_stores.id as chain_store_id,
local_chain_stores.id as local_chain_store_id,
chain_stores.website as website,
chain_stores.name as name,
chain_stores.cname as cname,
chain_stores.flyer_url as flyer_url,
local_chain_stores.city as city,
local_chain_stores.shopping_mall as shopping_mall,
local_chain_stores.postal_code as postal_code,
local_chain_stores.street_address as street_address,
to_tsvector('sv', chain_stores.name) ||
to_tsvector('sv', replace(chain_stores.name, ' ', '')) ||
to_tsvector('sv', local_chain_stores.city) ||
to_tsvector('simple', to_char(local_chain_stores.postal_code, '99999')) ||
to_tsvector('sv', coalesce(local_chain_stores.shopping_mall)) ||
to_tsvector('sv', local_chain_stores.street_address) ||
to_tsvector('sv', chain_stores.bio)
as document
FROM
local_chain_stores, chain_stores
WHERE
local_chain_stores.chain_store_id = chain_stores.id
");
这是我查询表格的方式:
$results = SearchableStore::
whereRaw("document @@ plainto_tsquery('simple', ?)", array($searchQuery->getQuery()))
->orWhereRaw("document @@ plainto_tsquery('sv', ?)", array($searchQuery->getQuery()))
->orderBy('name', 'asc')
->get();
return $results;
这段代码本来可以完美运行,但是如果有人拼错了单词,它就无法正常工作。我正在尝试解决这个问题。你还会注意到这两行代码:
to_tsvector('sv', chain_stores.name) ||
to_tsvector('sv', replace(chain_stores.name, ' ', '')) ||
我知道这看起来很丑,但我实际上正在确保如果有人搜索“BurgerKing”,我想返回“Burger King”的结果。我知道这看起来像一个愚蠢的例子,但在瑞典语中经常发生这些错误(不一定是针对Burger King,而是其他术语)。除此之外,我还希望确保当有人打错字时,我仍然希望返回相关的结果。
那么,我的问题是:我该如何实现这个功能?我尝试过模糊搜索,但我陷入了困境,因为我不知道如何查询由tsvector值组成的列(文档)。