Sort query by relevance MYSQLi

Navigation
0

How could you make the query below sorted by relevance and then by date? 

$query = $mysqli->prepare(
"SELECT 'id', 'titulo', 'foto', 'descricao', 'slug', 'data', '' as 'local' FROM 'noticias' WHERE MATCH ('titulo', 'descricao') AGAINST (?)
UNION
SELECT 'id',  'titulo', 'foto', '' as 'descricao', 'slug', 'data', 'local' FROM 'galeria' WHERE MATCH ('titulo', 'local') AGAINST (?) ORDER BY 'data' DESC"
);

Currently you are sorting by date, but wanted by relevance according to the search.

Example:

  

Currently if I search for "Feast of Mary" it is appearing like this:

  • Feast of Mary 09/12/2018
  • Party Any 10/09/2018
  • Feast of John 10/08/2018
  • Festa da Maria 07/10/2017
    •   

    I wanted it to look like this:

  • Feast of Mary 09/12/2018
  • Festa da Maria 07/10/2017
  • Party Any 10/09/2018
  • Feast of John 10/08/2018
    • asked by anonymous 12.09.2018 / 16:38

    1 answer

    1

    You would have to do something +/- like this: 

    SELECT 'id', 'titulo', 'foto', 'descricao', 'slug', 'data', '' as 'local', 
MATCH ('titulo', 'descricao') AGAINST (?) AS relevance 
FROM 'twd_noticias' 
WHERE MATCH ('titulo', 'descricao') AGAINST (?) 
UNION 
SELECT 'id', 'titulo', 'foto', '' as 'descricao', 'slug', 'data', 'local', 
MATCH ('titulo', 'local') AGAINST (?) AS relevance 
FROM 'twd_galeria' 
WHERE MATCH ('titulo', 'local') AGAINST (?) 
ORDER BY relevance + data DESC
        
    12.09.2018 / 19:19
    How to assign a Json source from an interface How to structure the parameters in a javascript object