Search code examples
sqlwhere-clause

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

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.