0

I have a table with id, topic ,description, image url as columns. Entries like

id   topic               description    image_url
1    People and blogs    TownHall       http://...
2    People and blogs 2  ......         .......
3    Blogs               .......        .......
4    Good                ......         ......

When I search blogs as a query input

SELECT * FROM people 
WHERE MATCH(topic,descript) AGAINST ("blogs" WITH QUERY EXPANSION)

I am getting results as

1  People and blogs    TownHall    http://.../
2  People and blogs 2  ......      ......./
3  Blogs               .......     ......./

The 3rd row should come first since it is the exact word (blogs) and the rest of row should come after this..

Please help me in debugging this.

Krishna
  • 65
  • 1
  • 9
  • What is the expected results? – Ivan Cachicatari May 07 '18 at 04:26
  • The that you are getting is correct because you are searching on topic with "blogs" and as you can see in the output, all three value for topic contains "blogs"... Are you expecting something else? then what? –  May 07 '18 at 04:32
  • I am expecting the exact word i.e blogs(3 rd row) should come first and then rest of the rows. – Krishna May 07 '18 at 04:36
  • You’re not telling the database how to sort them so it will return them in any order it happens to. You’ll need to tell it to order based on a function you want, for example relevance. – Sami Kuhmonen May 07 '18 at 04:38
  • I have to sort as u said. How to sort based on the matching relevance. – Krishna May 07 '18 at 04:42
  • I have posted an answer below check out, it will resolve your problem for sure. –  May 07 '18 at 05:52

2 Answers2

0

Try something like:

SELECT * FROM people, 
MATCH(topic,descript) AGAINST ("blogs" WITH QUERY EXPANSION) AS score
WHERE score > 0
ORDER BY score DESC
vogomatix
  • 4,856
  • 2
  • 23
  • 46
0

Try below SQL, it will solve your problem.

SELECT people.*
  FROM people
order by MATCH(topic,descript) AGAINST ("blogs" WITH QUERY EXPANSION) asc;

OUTPUT:

+------+--------------------+-------------+
| id   | topic              | descript    |
+------+--------------------+-------------+
|    3 | Blogs              | ........    |
|    2 | People and blogs 2 | ........    |
|    1 | People and blogs   | TownHall    |
+------+--------------------+-------------+
  • This answer may be right for this particular solution. But i think the relevance or matching percentage of "blogs" with the query word "blogs" is lesser compared to other in the above solution i.e u are ordering in asc. How come this is possible since it has exact match.. – Krishna May 08 '18 at 05:29