Raw Queries as a Last Resort

Let's finish this course chapter about querying the data from Eloquent by reminding you that there are also raw queries.

If you have some function in your database engine like MySQL, but it's not present in Eloquent, you need specifically the functions from MySQL. Of course, you can use that in raw queries.

For example, I have ten task records in the database and want to show only one month from the created_at field.

In the select, you can use DB::raw(); inside this method, use SQL functions.

use App\Models\Task;
use Illuminate\Support\Facades\DB;
 
$tasks = Task::select('id', 'description', DB::raw('MONTH(created_at) as created_month'))->get();
 
foreach ($tasks as $task) {
    dump($task->id . ': ' . $task->description . ' - month ' . $task->created_month);
}

Or, instead of using DB::raw(), you can use the selectRaw() method.

$tasks = Task::selectRaw('id, description, MONTH(created_at) as created_month')->get();
 
foreach ($tasks as $task) {
    dump($task->id . ': ' . $task->description . ' - month ' . $task->created_month);
}

The result will be the same, the code will only be shorter.

Also, you can do whereRaw() to make a where condition.

$tasks = Task::selectRaw('id, description, MONTH(created_at) as created_month')
    ->whereRaw('MONTH(created_at) = 2')
    ->get();
 
foreach ($tasks as $task) {
    dump($task->id . ': ' . $task->description . ' - month ' . $task->created_month);
}

The result is only tasks made in the second month.

And last, there is a orderByRaw.

$tasks = Task::selectRaw('id, description, MONTH(created_at) as created_month')
    ->orderByRaw('MONTH(created_at)')
    ->get();
 
foreach ($tasks as $task) {
    dump($task->id . ': ' . $task->description . ' - month ' . $task->created_month);
}

The only downside or thing you should be careful about that I will mention is parameters. If you use raw queries with parameters, be extremely careful because they are not protected from the SQL injection, by default.

Raw queries, in general, are a "last resort", only for specific cases. I just wanted to mention them in this course, so you would know that you can still write general SQL queries inside of Eloquent in Laravel.

Code for this lesson can be found on GitHub.