Using Raw Expressions for Updates in Laravel Query Builder

I recently ran into a problem where I needed to bulk update a table based on values in another column. I didn’t want to query for all the things, make the changes in PHP and save each one, but I also didn’t want to write raw MySql.

Enter Raw Expressions. They’re document but not fully (see https://laravel.com/docs/5.6/queries#raw-expressions), particularly for doing update queries, which I’ll focus on in this article.

Using Raw Expressions

What are Raw Expressions?

Raw Expressions allow you to tell the query builder that you want to use what you entered and not get processed or manipulate before running the query against the database. In Laravel 4.2 I used them for subselects.

They come in 2 forms, on the DB facade or an Expression class. So doing:

use Illuminate\Support\Facades\DB;
DB::raw('statement')

Would be the same as doing:

use Illuminate\Database\Query\Expression;
new Expression('statement')

I prefer the latter but for the sake of this article I’ll stick to the Facade as people are more familiar with it.

Using Raw Expressions in Update Queries

Say I was adding a new column to my transactions table called invoice_date which by default is 7 days before payment date I could do the following

use Illuminate\Support\Facades\DB;
DB::table('transactions')
    ->whereNull('invoice_date')
    ->update([
        'invoice_date' => DB::raw('DATE_SUB(`payout_date`, INTERVAL 3 DAY)')
    ])
;

Or as an Eloquent model:

use Illuminate\Support\Facades\DB;
Transaction::whereNull('invoice_date')
    ->update([
        'invoice_date' => DB::raw('DATE_SUB(`payout_date`, INTERVAL 3 DAY)')
    ])
;

Summary

We’ve seen how using Raw Expressions can be used in update queries to make doing unique bulk updates quicker and easier.