CODEWITHSUNDEEP
mysqlsqlsearch-engine
Martin AJ
Martin AJ

Reputation: 6697

How can I search into two columns and sort the result by priority?

I have a table like this:

-- users
+----+------------+-------------+
| id | first_name |  last_name  |
+----+------------+-------------+
| 1  | John       | Kooper      |
| 2  | Martin     | Koop        |
| 3  | Koop       | Sth         |
| 4  | Jack       | Linoff      |
+----+------------+-------------+

It's a table contains my website's users. Now I want to search in them. What's your query suggestion?

Here is my current query:

SELECT u.*
FROM users 
WHERE first_name LIKE :input%
   OR last_name LIKE :input%
   OR CONCAT(first_name, last_name) LIKE :input%

As you can see, my query just searches, nothing else. There isn't any ORDER BY clause, so no priority defined. You know, all I'm trying to do is implementing something like this search engine:

enter image description here

Anyway does anybody how can I implement a priority to my query? For example, if you search Koop, the first row of the result should be the second row in the table, because last_name column should has a bigger priority (than first_name column) and koop is the exact value of last_name for the second row of the table.

Here is the expected result for :input = 'koop':

+----+------------+-------------+
| 2  | Martin     | Koop        |
| 1  | John       | Kooper      |
| 3  | Koop       | Sth         |
+----+------------+-------------+

How can I do that?

Upvotes: 1

Views: 400

Answers (3)

Sergej
Sergej

Reputation: 2196

Consider to use FULLTEXT search.

SELECT *,
    MATCH (`first_name`) AGAINST (':input*' IN BOOLEAN MODE) AS `relevance_first_name`,
    MATCH (`last_name`) AGAINST (':input*' IN BOOLEAN MODE) AS `relevance_last_name`
FROM `users`
WHERE MATCH (`first_name`, `last_name`) AGAINST (':input*' IN BOOLEAN MODE)
ORDER BY (`relevance_first_name`)+(`relevance_last_name`*4) DESC

Upvotes: 1

osanger
osanger

Reputation: 2362

A simple solution could be a (switch) case statement inside the order by clause like this:

SELECT u.*
FROM users 
WHERE first_name LIKE :input%
OR last_name LIKE :input%
OR CONCAT(first_name, last_name) LIKE :input%     
ORDER BY 
(CASE WHEN first_name = :input THEN 1 
      WHEN first_name LIKE :input% THEN 2 (....) ELSE 99 END) ;

Upvotes: 0

user8290063
user8290063

Reputation:

You can use ORDER BY columns... For example, you can sort first by last name, then by first name.

SELECT u.* FROM users 
    WHERE first_name LIKE :input%
        OR last_name LIKR :input%
        OR CONCAT(first_name, last_name) LIKE :input%
    ORDER BY last_name, first_name;

Update From your comments, if you want to sort by priority, which is rating or anything else, you should user ORDER BY users._column_name or join another table where rating column is, and order the result by this column.

Upvotes: 0

Related Questions