Home SQL – is there a way to order results by how many `OR`'s it maches? (laravel)

Questions

SQL – is there a way to order results by how many `OR`'s it maches? (laravel)

March 22, 2023

I have this query:

SELECT * FROM articles
    WHERE name LIKE '%test%'
OR
    title LIKE '%test%'
OR
    text LIKE '%test%'
OR
    author LIKE '%test%'

The (known) results are composed of records that match the first condition, others that match the second and so on..
However – some of them will match more than one condition.

I want to order them by this order – those who match all 4 where‘s first, those who match three where‘s will come second and so on…

I know that the mechanism is preventing it because after the first match, the others are not tested.. is there a way to do it anyway?

I use laravel, so if there is a way to achieve it with laravel, it will do as well.

>Solution :

You could use something like this:

SELECT *
FROM articles
WHERE name LIKE '%test%'
   OR title LIKE '%test%'
   OR text LIKE '%test%'
   OR author LIKE '%test%'
ORDER BY 
    (case when name LIKE '%test%'then 1 else 0 end) + 
    (case when title LIKE '%test%' then 1 else 0 end) +
    (case when text LIKE '%test%' then 1 else 0 end) +
    (case when author LIKE '%test%' then 1 else 0 end) desc

For different DBMSs you could simplify this a bit. IF in MySQL, as an example.