Skip to content

Instantly share code, notes, and snippets.

@imliam
Last active August 22, 2023 02:16
Show Gist options
  • Save imliam/8c742621ee77dd8ba64bbebad1db4ebc to your computer and use it in GitHub Desktop.
Save imliam/8c742621ee77dd8ba64bbebad1db4ebc to your computer and use it in GitHub Desktop.
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment