weighted-search

[MwSpaceLLC]

Hi there, today I'm going to create with my teame query for order by case as follow:

SELECT ragione_sociale, localita, nazione, macrocategoria, microcategoria, IF( `ragione_sociale` LIKE "bar%", 20, IF(`ragione_sociale` LIKE "%bar%", 10, 0) ) + IF(`macrocategoria` LIKE "%bar%", 5, 0) + IF(`microcategoria` LIKE "%bar%", 1, 0) AS `weight` FROM anagrafiche WHERE ( `ragione_sociale` LIKE "%bar%" OR `macrocategoria` LIKE "%bar%" OR `microcategoria` LIKE "%bar%" ) ORDER BY weight DESC

OR:

SELECT country_code,ragione_sociale, localita, nazione, macrocategoria, microcategoria FROM anagrafiche WHERE ( `ragione_sociale` LIKE "%bar%" OR `macrocategoria` LIKE "%bar%" OR `microcategoria` LIKE "%bar%" ) ORDER BY CASE WHEN `ragione_sociale` LIKE "% bar %" THEN ragione_sociale WHEN `macrocategoria` LIKE "%bar%" THEN macrocategoria WHEN `microcategoria` LIKE "%bar%" THEN microcategoria END DESC

OR:

SELECT ragione_sociale, localita, nazione, macrocategoria, microcategoria, IF(MATCH (ragione_sociale) AGAINST ('bar' IN NATURAL LANGUAGE MODE ), 20, 0) + IF(MATCH (macrocategoria) AGAINST ('bar' IN NATURAL LANGUAGE MODE), 5, 0) + IF(MATCH (microcategoria) AGAINST ('bar' IN NATURAL LANGUAGE MODE), 1, 0) AS `weight` FROM anagrafiche WHERE country_code = 'IT' AND ( MATCH (ragione_sociale) AGAINST ('bar' IN NATURAL LANGUAGE MODE) OR MATCH (macrocategoria) AGAINST ('bar' IN NATURAL LANGUAGE MODE) OR MATCH (microcategoria) AGAINST ('bar' IN NATURAL LANGUAGE MODE) ) ORDER BY weight DESC LIMIT 100

We want use loopback query but we can't find the correct method in the guide.

The aspected records order depends by result in query (match like fulltextindex)

Tnks for any reply 🤖