MySQL Query Text Across Multiple Tables

I have two MySQL Tables; City and Country, I want to allow a user to search across the two tables for something which matches in either, and I want a quality match/relevancy order from both tables.

(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);

I get a combined result across the two tables, but I get all the city results, then all the country results – I want to get it so the results are mixed and sorted by accuracy… not accuracy of city, then accuracy of country – or is that desirable?

Any other ways to accomplish a query across two tables would be especially welcome, ideally I might need to join a table onto city and a table onto country to add some meta information to the data returned.

Or is there a better way to call the two endpoints and have a relevancy match column, and then merge both endpoints into a json array server side ordered by this relevancy?

Answer

You can wrap the UNION into a subquery and then do the ordering again:

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 

It would be more efficient to create a ranking column:

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

This will give you 48 results, 24 each from city and country. If you just want the top-ranked results, regardless of which table they came from, just remove the ORDER BY and LIMIT clauses from the 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