Как искать несколько столбцов с приоритетом в MySQL?

Question

В настоящее время у меня есть функция PHP (codeigniter), которая принимает строку запроса (из post) и возвращает соответствующие строки MySQL, например:

$term = $this->input->post("query");
$rows = $this->db->query("SELECT id, title as 'desc' , abbreviation , etc FROM shows WHERE abbreviation LIKE '%".$term."%' OR title LIKE '%".$term."%' OR etc LIKE '%".$term."%'  LIMIT 10; ")->result_array();

это работает, но допустим, моя таблица выглядит так:

id  title                    abbreviation
--  ----------------------   ------------
1   The Big Bang Theory      TBBT
2   How I Met Your Mother    HMYM
3   a show called tbbt hmym  ASCTM

Теперь, когда пользователь ищет tbbt, он возвращает 3-й ряд первым. Но я хочу, чтобы аббревиатура была основным параметром.

Поэтому, если аббревиатура соответствует, сначала верните ее, а затем найдите заголовок столбца.

Я думаю, что могу сделать это с помощью PHP, вот так:

// pseudo-code : 
$abbreviation_matches = result("bla bla WHERE abbreviation LIKE term AND NOT title like term");
$title_matches = result("bla bla WHERE NOT abbreviation LIKE term AND title LIKE term");
$result = append($abbreviation_matches , $title_matches);

Но я боюсь, что это не будет так эффективно, так как включает в себя 2 отдельных запроса SQL. Так есть ли более эффективный способ сделать это? Желательно в одном запросе?

И реальный случай с 4 столбцами, но только один столбец имеет приоритет, поэтому остальные не важны по порядку.

Спасибо за любую помощь!

Answer 1

Это может быть одно из решений:

SELECT id, desc, abbreviation, etc FROM
( 
   SELECT 1 AS rnk,  id, title as 'desc' , abbreviation , etc FROM shows 
   WHERE abbreviation LIKE '%".$term."%'
   UNION 
   SELECT 2 AS rnk,  id, title as 'desc' , abbreviation , etc FROM shows 
   WHERE title LIKE '%".$term."%' 
) tab
ORDER BY rnk
LIMIT 10;

Или вы можете сделать то же самое с помощью следующего:

SELECT  id, desc, abbreviation, etc FROM
(
    SELECT CASE WHEN abbreviation LIKE '%".$term."%' THEN 1 ELSE 2 END As rnk,
    id, title AS desc, abbreviation, etc 
    FROM shows 
    WHERE abbreviation LIKE '%".$term."%' 
    OR title LIKE '%".$term."%' 
) tab
 ORDER BY rnk
 LIMIT 10;