Sort query by Mysqli relevance

Asked

Viewed 114 times

0

how the query below could be ordered 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 ordering by date, but wanted by relevance according to the search.

Example:

Currently if I search "Maria’s Feast" is appearing like this:

  1. Feast of Mary 12/09/2018
  2. Any party 10/09/2018
  3. Feast of John 10/08/2018
  4. Feast of Mary 10/07/2017

I wanted it to stay that way:

  1. Feast of Mary 12/09/2018
  2. Feast of Mary 10/07/2017
  3. Any party 10/09/2018
  4. Feast of John 10/08/2018
php mysql mysqli match full-text

  • then wouldn’t be order by titulo, data DESC?

  • I tried, but it didn’t work.

  • ORDER BY relevance DESC can help you... however it will order the first result_set separate from the second

  • @fernandosavio could not make it work, because in the other example was only 1 table and not two as I am doing.

  • Yeah, he’d just sort a select... to sort the two together I guess I’d have to turn your Unions into a subquery and sort in select more from outside.

  • @fernandosavio this link here has the answer (I think), but I could not adapt to my query. Could you help me? https://stackoverflow.com/questions/9018577/mysql-fulltext-search-relevance-across-multiple-tables

  • Actually what I said is exactly what @Matheusribeiro posted in response. He turned your query into a subquery and ordered "out" of the subquery

  • The only difference is that in the answer you linked there is a field relevance which is added to select and is used to sort.

  • Yes, but as I told him, I need to order by relevancy of the result and not necessarily in alphabetical order. For example, I need "Rosario party" if I sort by alphabetical order this result may come after "cool party" because the 2 has the term party and by alphabetical order the result "cool party" would come first

Show 4 more comments

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

  • Like this <b>Fatal error</b>: Call to a Member Function bind_param() on Boolean in

  • Check my change!

  • Same error, I believe it is because of $mysql -> prepare

  • In my answer I’m using single quotes in each field and I didn’t put the script in double quotes... Did you set it right in your font? Or just copied and pasted what I did?

  • I left the general inside the double quotes and it didn’t work.

  • $query = $mysqli->prepare( "select * from (SELECT 'id', 'title', 'photo', 'Description', 'Slug', 'data', '''place' FROM 'news' WHERE MATCH ('title', 'Description') AGAINST (?) UNION SELECT 'id', 'title', 'photo', ''Description', 'Slug', 'data', 'place' FROM 'gallery' WHERE MATCH ('title', 'place') AGAINST (?)) as tabela ORDER BY title, data DESC");

  • Run this select directly in your database and see if it works correctly.

  • I found the error, but it is not ordering correctly. Needed to sort according to search relevancy and not necessarily in ascending or descending alphabetical order.

  • Let’s go continue this discussion in chat.

  • Vlw Matheus. The result is now satisfactory

Show 5 more comments

Browser other questions tagged php mysql mysqli match full-text

You are not signed in. Login or sign up in order to post.