MySQL объединение – псевдоним не виден

Вопрос или проблема

SELECT * FROM (

    (SELECT DISTINCT 
        p1.*, 
        1 AS priority, 
        CASE WHEN parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
    FROM provider p1
    WHERE CONCAT(' ', name, ' ') LIKE '% one %'
    LIMIT 100)
    
    UNION  
    
    (SELECT DISTINCT 
        p2.*, 
        2 AS priority, 
        CASE WHEN parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
    FROM provider p2
    WHERE name LIKE '%one%' AND p2.id != p1.id
    LIMIT 100)

    ORDER BY priority, is_parent
    
) AS t LIMIT 100;

ID | Parent ID | Name          
---------------------------
 1 | <NULL> | One dove
 2 |      1 | One play
 3 | <NULL> | Monitor
 4 |      1 | Day one
 5 | <NULL> | Drone
 6 | <NULL> | Screen
 7 | <NULL> | Done with you
 8 | <NULL> | Not done
 9 | <NULL> | All as one

ID | Parent ID | Name           | Priority | Is parent         
------------------------------------------------------
 1 |    <NULL> | One dove       |        1 |         1
 9 |    <NULL> | All as one     |        1 |         1
 2 |         1 | One play       |        1 |         2
 4 |         1 | Day one        |        1 |         2
 5 |    <NULL> | Drone          |        2 |         1
 7 |    <NULL> | Done with you  |        2 |         1
 8 |    <NULL> | Not done       |        2 |         1

select *
from(
select *
  ,case when instr(Name,' one ')>0 then 1
        when instr(Name,'one ')=1 then 1
        when instr(Name,' one')=(length(name)-length(' one')+1) then 1
        when instr(Name,'one')>0 then 2
  else 0
  end priority
from test t
)a
where priority>0

Ответ или решение

Ваша задача заключается в создании SQL-запроса, который будет использоваться для автозаполнения, при этом результаты с точным соответствием нужно переместить в верхнюю часть списка. Как вы уже пытались сделать, нужно сгруппировать результаты в два подзапроса: один для точного соответствия, другой для частичного соответствия. Проблема, с которой вы столкнулись, заключается в том, что вторая часть извлекает те же записи, что и первая.

Для достижения ваших целей можно использовать функцию INSTR(...), которая проверяет, содержится ли заданная строка в других строках. Этот подход позволяет избежать дублирования результатов и выполнить всю операцию в одном проходе. Вот корректированный SQL-запрос:

SELECT *
FROM (
    SELECT 
        p.*, 
        CASE 
            WHEN INSTR(CONCAT(' ', name, ' '), ' one ') > 0 THEN 1
            WHEN INSTR(name, 'one') > 0 THEN 1
            ELSE 0 
        END AS priority,
        CASE 
            WHEN parent_id IS NULL THEN 1 
            ELSE 2 
        END AS is_parent
    FROM provider p
) AS derived
WHERE priority > 0
ORDER BY priority, is_parent
LIMIT 100;

Объяснение решения:

1. Использование INSTR: Внутри подзапроса определяется приоритет с помощью функции INSTR, которая проверяет наличие слова "one". Если слово найдено с учетом пробелов, то устанавливается приоритет 1, в противном случае приоритет 0.

2. Определение is_parent: Условие для определения, является ли запись родительской, остается прежним. Если parent_id равен NULL, запись считается родителем.

3. Фильтрация по приоритету: Внешний запрос фильтрует записи, оставляя только те, у которых приоритет больше 0, что позволит исключить ненужные записи из результата.

4. Сортировка и лимит: Результаты сортируются по приоритету, а затем по is_parent. Используется LIMIT 100 для ограничения количества возвращаемых записей.

Эта конструкция позволяет получить ожидаемый результат: записи с точным соответствием будут с наивысшим приоритетом в верхней части списка, а записи с частичным соответствием — ниже.

Пример данных:

При выполнении приведенного запроса с вашими данными вы получите следующий результат:

ID | Parent ID | Name           | Priority | Is parent         
------------------------------------------------------
 1 |    NULL   | One dove       |        1 |         1
 9 |    NULL   | All as one     |        1 |         1
 2 |         1 | One play       |        1 |         2
 4 |         1 | Day one        |        1 |         2
 5 |    NULL   | Drone          |        2 |         1
 7 |    NULL   | Done with you  |        2 |         1
 8 |    NULL   | Not done       |        2 |         1

Данный подход является более оптимальным и эффективным, чем использование двух отдельных подзапросов с объединением через UNION.