CODEWITHSUNDEEP
mysqlsortingfull-text-searchsql-order-by
Shafizadeh
Shafizadeh

Reputation: 10350

How to set priority for columns and sort the results by that priority?

I have a table like this:

// mytable
+----+---------+-------------------------------+
| id |   title |           content             |
+----+---------+-------------------------------+
| 1  |  hello  |  how are you?                 |
| 2  |  you    |  it is  content2              |
| 3  |  what   |  hello is a word for greating |
| 4  |  mouse  |  it is content4               |
+----+---------+-------------------------------+

Well, I want to give a more priority to title than content. I mean is, I want to show all result from title column (in first) and then show all results from content column (in second). Also here is my query:

select * from mytable where match(title, content) against($word);

Also here is two examples:

Example1:

$word = 'you';

I want this output: (focus on the sort)

+----+---------+-------------------------------+
| id |   title |           content             |
+----+---------+-------------------------------+
| 2  |  you    |  it is  content2              |
| 1  |  hello  |  how are you?                 |
+----+---------+-------------------------------+

Example2:

$word = 'hello';

I want this output: (focus on the sort)

+----+---------+-------------------------------+
| id |   title |           content             |
+----+---------+-------------------------------+
| 1  |  hello  |  how are you?                 |
| 3  |  what   |  hello is a word for greating |
+----+---------+-------------------------------+

I emphasize again, I want all result from column of title and after it all results from from column of content. Is there any solution?

Upvotes: 0

Views: 2405

Answers (3)

John Ruddell
John Ruddell

Reputation: 25862

all you need to do is a conditional order by with a CASE and a match off of the word. Here is an example to get you going

SELECT title, content
FROM tablename
ORDER BY CASE
    WHEN title LIKE '%you%' THEN 1
    WHEN content LIKE '%you%' THEN 2
    ELSE 3
END;

FIDDLE EXAMPLE

Upvotes: 5

Shadow
Shadow

Reputation: 34285

Run 2 separate queries against the 2 columns and combine them with union into single resultset:

select * from mytable where match(title) against($word);
union distinct
select * from mytable where match(content) against($word);

EDIT:

If you do not like the union approach, then create 3 fulltext indexes, onne on title, one on content, and one one the co bination of the 2. Then use the following approach:

SELECT title, content,
  MATCH (title) AGAINST ($word) AS c1,
  MATCH (content) AGAINST ($word) AS c2
FROM tablename
WHERE MATCH (title,content) AGAINST ($word)
ORDER BY IF(c1> 0,1,0)

The combined index would be used for searching, while the individual indexes would be used to produce the weighting. However, if a word is found too many times, its weight may be reduced to 0.

Upvotes: 0

DCR
DCR

Reputation: 15700

seems like your almost there. How about:

select * from mytable where match(title) against($word);
union
select * from mytable where match(content) against($word);

Upvotes: -1

Related Questions