CODEWITHSUNDEEP
mysqlsqlunion
Erwan
Erwan

Reputation: 92

SQL - Priority using UNION

I am currently working on a search engine for a website. I have a table Movies :

| id_movie | title                    | subtitle                   | primary_keyword     | keywords              | reference |
|-----------|--------------------------|----------------------------|----------------------|-----------------------|-----------|
| 1         | The Lord of the Rings    | The Fellowship of the Ring | lord rings           | lord rings fellowship | LORFR     |
| 2         | The Lord of the Rings    | The Two Towers             | lord rings           | lord two towers       | LORTT     |
| 3         | The Lord of the Rings    | The Return of the King     | lord                 | return king           | LORRK     |
| 4         | The Shawshank Redemption |                            | shawshank redemption | shawshank redemption  | SSR       |

(This is only an example)

I want to make a search with this priority : 1 - Reference 2 - Title 3 - Keywords 4 - Primary Keyword 5 - Subtitle

This is what I have:

SELECT * FROM (
SELECT 1 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  reference LIKE '%rings%'
UNION 
SELECT 2 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  title LIKE '%rings%'
UNION 
SELECT 3 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  keywords LIKE '%rings%'
UNION 
SELECT 4 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  primary_keyword LIKE '%rings%'
UNION 
SELECT 5 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  subtitle LIKE '%rings%') as BigSelect
 ORDER BY prio ASC

It works, but what I want is : if two movies has 'rings' in the title, that the next priority is keywords Then if they have both 'rings' in keywords check primary_keyword ....

The final result I want is :

| title                     | subtitle                   | reference |
|---------------------------|----------------------------|-----------|
| The Lord of the Rings     | The Fellowship of the Ring | LORFR     |
| The Lord of the Rings     | The Two Towers             | LORTT     |
| The Lord of the Rings     | The Return of the King     | LORRK     |

The three 'Lord of the Rings' have 'rings' in the title. Then only 'The Fellowship of the Ring' has 'rings' in the keywords Then 'The Two Towers' has 'rings' in the primary_keyword (not The Return of the King)

Thank you !

Upvotes: 0

Views: 202

Answers (3)

Barmar
Barmar

Reputation: 780879

You can add up the priorities of matching different columns, and use that as the priority to order by.

SELECT 10000 * (reference LIKE '%rings%') +
        1000 * (title LIKE '%rings%') +
         100 * (keywords LIKE '%rings%') + 
          10 * (primary_keyword LIKE '%rings%') +
           1 * (subtitle LIKE '%rings%') AS prio,
    title, subtitle, reference
FROM movies
WHERE reference LIKE '%rings%' OR title LIKE '%rings%' OR keywords LIKE '%rings%' OR primary_keyword LIKE '%rings%' OR subtitle LIKE '%rings%'
ORDER BY prio DESC

DEMO

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Use order by with multiple keys:

SELECT title, subtitle, reference
FROM movies
ORDER BY (reference LIKE '%rings%') DESC,
         (title LIKE '%rings%') DESC,
         (keywords LIKE '%rings%') DESC, 
         (primary_keyword LIKE '%rings%') DESC,
         (subtitle LIKE '%rings%') DESC;

MySQL conveniently treats boolean expressions as numbers with "1" for true and "0" for false. Hence, DESC puts the matches first.

Upvotes: 2

Erwan
Erwan

Reputation: 92

So, this is what I did, but I think it could be optimized since there is a lot a repetition :

SELECT title, subtitle, reference
 FROM   movies 
 WHERE  reference LIKE '%rings%' OR title LIKE '%rings%' OR keywords LIKE '%rings%' OR primary_keyword LIKE '%rings%' OR subtitle LIKE '%rings%'
ORDER BY
CASE
WHEN reference LIKE '%rings%' AND title IKE '%rings%' AND keywords LIKE '%rings%' AND primary_keywords LIKE '%rings%' AND subtitle LIKE '%rings%' THEN 1
WHEN reference LIKE '%rings%' AND title IKE '%rings%' AND keywords LIKE '%rings%' AND primary_keywords LIKE '%rings%' THEN 2
WHEN reference LIKE '%rings%' AND title IKE '%rings%' AND keywords LIKE '%rings%' THEN 3
WHEN reference LIKE '%rings%' AND title IKE '%rings%' THEN 4
WHEN reference LIKE '%rings%' THEN 5
WHEN title LIKE '%rings%' AND keywords LIKE '%rings%' AND primary_keywords LIKE '%rings%' AND subtitle LIKE '%rings%' THEN 6
WHEN title LIKE '%rings%' AND keywords LIKE '%rings%' AND primary_keywords LIKE '%rings%' THEN 7
WHEN title LIKE '%rings%' AND keywords LIKE '%rings%' THEN 8
WHEN title LIKE '%rings%' THEN 9
WHEN keywords LIKE '%rings%' AND primary_keywords LIKE '%rings%' AND subtitle LIKE '%rings%' THEN 10
WHEN keywords LIKE '%rings%' AND primary_keywords LIKE '%rings%' THEN 11
WHEN keywords LIKE '%rings%' THEN 12
WHEN primary_keywords LIKE '%rings%' AND subtitle LIKE '%rings%' THEN 13
WHEN primary_keywords LIKE '%rings%' THEN 14
WHEN subtitle LIKE '%rings%' THEN 15
END

Upvotes: 0

Related Questions