Forgive me if this is a silly question.
I have the following code in one of my migrations:
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
");
And this is how I query the table:
$results = SearchableStore::
whereRaw("document @@ plainto_tsquery('simple', ?)", array($searchQuery->getQuery()))
->orWhereRaw("document @@ plainto_tsquery('sv', ?)", array($searchQuery->getQuery()))
->orderBy('name', 'asc')
->get();
return $results;
This works perfectly fine, unless someone spells some word(s) wrong. And that's what I'm trying to solve. You will also notice these 2 lines:
to_tsvector('sv', chain_stores.name) ||
to_tsvector('sv', replace(chain_stores.name, ' ', '')) ||
I know this is ugly, but what I am essentially doing is making sure that if someone does a search for e.g "BurgerKing" I want to return results for "Burger King". I know this looks like a silly example, but these mistakes happen a lot in the swedish language (not necessarily for Burger King, but other terms). Aside from that, I also want to make sure that when someone does a typo I still want to return relevant results.
So, my question is. How do I implement this? I tried playing around with fuzzy search, but I'm kind of stuck because I don't know how to query a column (document) consisting of tsvector values.
