Implementing Custom Logic With Raw SQL In Laravel’s Query Builder

Laravel’s query builder offers a nice way to work with raw SQL. We can use them in our where conditions and also in our orderings as well. Let’s see some examples where we can use raw SQL to implement custom logic for ordering the results.

Before we start, it’s important to understand, that ordering your query and sorting your results are two different things. When you add an order statement to your query builder, it affects the query itself. When you are sorting your results, it affects your collection instance, but with the results you already have.

// Query builder sorting
Post::orderBy('name')->get();

// Collection sorting
Post::get()->sortBy('name');

It’s important to understand this since if you are adding an order statement to your query, you can get totally different results. But if you are sorting your results, it will order the items, but they will stay the same.

Simple Raw SQL

Let’s start with some easier stuff. Let’s say we want to order the results by the length of the column’s value. For example the title column. With raw SQL we easily can implement this logic:

Post::orderByRaw('length(title)')->get();

It’s a good representation of how much flexibility we have here. Laravel’s query builder offers a nice way to work with SQL and it’s very flexible. But sometimes it’s not enough. In some cases, we need to bring our custom logic and use them with the query builder.

Implementing the Custom Logic

So, usually we order by columns we have on the table, but in some cases, we need to order our records with a custom logic. The basic idea, we make a custom SQL condition, if the condition is true then we get 1 else we get 2. Then we can order our records due to the result of the condition. Let’s see the condition:

Post::orderByRaw('case when title like ? then 1 else 2 end', ["{$title}%"])->get();

In this case, we want to order the posts by this custom condition. If the title starts with the given string, then the result will be 1 otherwise 2. By default, it has an ascending order, but you can add DESC at the end of the condition to change the order.

Don’t forget, you can chain raw methods as well with the query builder.

You can read more about Laravel and raw SQL at the documentation.

Special thanks for the following recource(s): Icon made by Freepik from www.flaticon.com