weighted-search.sql

-- Weigh rows against eachother based on different conditions,
-- ordering the results based on their given weights so that
-- more precise matches will show higher up in the results.

-- In this example, an exact match will show up at the top
-- of the results, a match at the beginning of the string
-- will show next, and a match anywhere will show last.

set @query = 'Liam';

select
    *,
    (case
        when email = @query then 5
        when email like concat(@query, '%') then 4
        when first_name = @query then 4
        when last_name = @query then 4
        when email like concat('%', @query, '%') then 3
        when first_name like concat(@query, '%') then 3
        when last_name like concat(@query, '%') then 3
        when first_name like concat('%', @query, '%') then 2
        when last_name like concat('%', @query, '%') then 2
        else 0
    end) as search_weight
from users
having search_weight > 0
order by search_weight desc

-- In this example, the weights of each column will add up
-- and stack together, so if a term shows up in multiple
-- places, it will rank higher in the ordered results.

select
    *,
    (
        (case when email = @query then 5 else 0 end) +
        (case when email like concat(@query, '%') then 4 else 0 end) +
        (case when email like concat('%', @query, '%') then 3 else 0 end) +
        
        (case when first_name = @query then 5 else 0 end) +
        (case when first_name like concat(@query, '%') then 3 else 0 end) +
        (case when first_name like concat('%', @query, '%') then 2 else 0 end) +
        
        (case when last_name = @query then 5 else 0 end) +
        (case when last_name like concat(@query, '%') then 3 else 0 end) +
        (case when last_name like concat('%', @query, '%') then 2 else 0 end)
    ) as search_weight
from users
having search_weight > 0
order by search_weight desc;