How to prioritize result in laravel eloquent

I have a table with 4 columns. I want to display the row based on input and it should be able to display with priority according to the priority fields.

example: My table looks like this:

ID  title          description    tags
1   web developer  designer       front-end
2   designer       front-end      web developer
3   front-end      web developer  designer

I have my test cases below how it should work:

$input = 'web developer' // the sequence should be IDs : 1, 3, 2
$input = 'front-end'     // output sequence result is : 3, 2, 1
$input = 'designer'      // result sequence : 2, 1, 3

Now, I want to prioritize the result from title down to tags based in the input.

$blog = DB::('blogs')
        ->where('title', 'LIKE', '%$title%')
        ->where('description', 'LIKE', '%$title%')
        ->where('tags', 'LIKE', '%$title%');

But the above codes, seems that it didn’t..

Answer

I think the logic you want to order your result should be appearing in an ORDER BY clause. Consider the following raw query:

SELECT *
FROM yourTable
ORDER BY
    CASE WHEN title = 'web developer'       THEN 0 ELSE 1 END,
    CASE WHEN description = 'web developer' THEN 0 ELSE 1 END,
    CASE WHEN tags = 'web developer'        THEN 0 ELSE 1 END;

Output:

Here is my attempt at what the Laravel code would look like using orderByRaw:

$orderByClause  = "CASE WHEN title = '".$input."' THEN 0 ELSE 1 END,";
$orderByClause .= "CASE WHEN description = '".$input."' THEN 0 ELSE 1 END,";
$orderByClause .= "CASE WHEN tags = '".$input."' THEN 0 ELSE 1 END";

$blog = DB::('blogs')
        ->orderByRaw($orderByClause)
        ->get();

I couldn’t find any (working) documentation on how to parameterize an orderByRaw, so I used string concatenation instead.

Demo here:

Rextester