Incorrect search ranking

[abstractionfactory]

The current search ranking is less than optimal and should be improved.

[cam72cam]

WITH search_terms AS (
  SELECT unnest(regexp_split_to_array('vsphere', '[ /]+')) AS term
),
term_matches AS (
  SELECT e.*
  FROM entities e
  INNER JOIN search_terms st
    ON e.addr ILIKE '%' || st.term || '%'
    OR e.description ILIKE '%' || st.term || '%'
  GROUP BY e.id
),
ranked_entities AS (
  SELECT *,
    CASE WHEN link_variables->>'namespace' = 'hashicorp' THEN 1 WHEN link_variables->>'namespace' = 'opentofu' THEN 0 ELSE 0.5 END as popularity_fudge,
    similarity(tm.addr, 'vsphere') as title_sim,
    similarity(tm.description, 'vsphere') as description_sim,
    similarity(link_variables->>'name', 'vsphere') as name_sim
  FROM term_matches tm
),
providers AS (
  SELECT *
  FROM ranked_entities
  WHERE type LIKE 'provider%'
  ORDER BY
    popularity_fudge DESC,
    title_sim DESC,
    name_sim DESC,
    description_sim DESC
  LIMIT 10
),
modules AS (
  SELECT *
  FROM ranked_entities
  WHERE type LIKE 'module%'
  ORDER BY
    popularity_fudge DESC,
    title_sim DESC,
    name_sim DESC,
    description_sim DESC
  LIMIT 10
)
select * from providers
UNION ALL
select * from modules;

[abstractionfactory]

Thanks @cam72cam with some tweaks this partially fixed it, still need to add the popularity ranking later.

[diofeher]

@cam72cam I think we can close this issue since the popularity ranking is implemented here https://github.com/opentofu/registry-ui/blob/main/search/worker/src/query.ts#L22

Feel free to reopen if there's something I've missed.

[diofeher]

I'm reopening since I just tested with cloudflare and cloudflare/cloudflare appears as the third result instead of being the first one:

[diofeher]

It seems the rank is right locally, production code is not up to date. I'll leave this ticket open until we deploy it there.

[diofeher]

Fixed with 50e7c1b and deployed. Closing the ticket.