Текст запроса MySQL в нескольких таблицах
У меня есть две таблицы MySQL; Город и страна, я хочу, чтобы пользователь мог искать в двух таблицах что-то, что соответствует любой из них, и мне нужен порядок соответствия/релевантности качества из обеих таблиц.
(SELECT 'city' AS type, c.slug, c.name, c.city_id AS id
FROM city c
WHERE c.name LIKE '%ame%'
ORDER BY CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END,
c.name ASC
LIMIT 24 OFFSET 0)
UNION
(SELECT 'country' as type, c.slug, c.name, c.country_id AS id
FROM country c
WHERE c.name LIKE '%ame%'
ORDER BY CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END,
c.name ASC
LIMIT 24 OFFSET 0);
Я получаю объединенный результат по двум таблицам, но я получаю все результаты по городу, затем все результаты по стране - я хочу получить его, чтобы результаты смешивались и сортировались по точности... не по городу, а по стране - или это желательно?
Особенно приветствуются любые другие способы выполнения запроса по двум таблицам, в идеале мне может потребоваться объединить таблицу с городом и таблицу со страной, чтобы добавить некоторую метаинформацию к возвращаемым данным.
Или есть лучший способ вызвать две конечные точки и иметь столбец соответствия релевантности, а затем объединить обе конечные точки в серверную часть массива json, упорядоченную по этой релевантности?
Ответы 1
Вы можете обернуть UNION в подзапрос, а затем повторить порядок:
SELECT *
FROM ((SELECT 'city' AS type, c.slug, c.name, c.city_id AS id
FROM city c
WHERE c.name LIKE '%ame%'
ORDER BY CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END,
c.name ASC
LIMIT 24 OFFSET 0)
UNION
(SELECT 'country' as type, c.slug, c.name, c.country_id AS id
FROM country c
WHERE c.name LIKE '%ame%'
ORDER BY CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END,
c.name ASC
LIMIT 24 OFFSET 0)) c
ORDER BY CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END,
c.name ASC
Было бы более эффективно создать столбец ранжирования:
SELECT *
FROM ((SELECT 'city' AS type, c.slug, c.name, c.city_id AS id,
CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END AS `rank`
FROM city c
WHERE c.name LIKE '%ame%'
ORDER BY `rank`, c.name ASC
LIMIT 24 OFFSET 0)
UNION
(SELECT 'country' as type, c.slug, c.name, c.country_id AS id,
CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END AS `rank`
FROM country c
WHERE c.name LIKE '%ame%'
ORDER BY `rank`, c.name ASC
LIMIT 24 OFFSET 0)) c
ORDER BY c.rank, c.name ASC
Это даст вам 48 результатов, по 24 в каждом городе и стране. Если вам просто нужны результаты с самым высоким рейтингом, независимо от того, из какой таблицы они получены, просто удалите предложения ORDER BY и LIMIT из UNION:
SELECT *
FROM ((SELECT 'city' AS type, c.slug, c.name, c.city_id AS id
FROM city c
WHERE c.name LIKE '%ame%')
UNION
(SELECT 'country' as type, c.slug, c.name, c.country_id AS id
FROM country c
WHERE c.name LIKE '%ame%'
)) c
ORDER BY CASE
WHEN c.name = 'ame' THEN 0
WHEN c.name LIKE 'ame%' THEN 1
WHEN c.name LIKE '%ame%' THEN 2
WHEN c.name LIKE '%ame' THEN 3
ELSE 4 END,
c.name ASC
LIMIT 48 OFFSET 0