How to Get Real SQL Query Under Eloquent Statement?

Developers often get syntax errors from Eloquent, like "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...". But it's hard to debug the SQL error without seeing the real SQL query.

Imagine we have this query:

$users = User::where('created_at', '<', now()->subYear())
    ->orderBy('email', 'asc')
    ->limit(5)
    ->get();

How to get the real SQL query under the hood and see it on the screen?

Option 1. Laravel 10+

If you are on Laravel 10.15 or newer, just change ->get() to ->ddRawSql():

$users = User::where('created_at', '<', now()->subYear())
    ->orderBy('email', 'asc')
    ->limit(5)
    ->ddRawSql();

It will show this:

"select * from `users` where `created_at` < '2022-08-13 12:06:16' order by `email` asc limit 5"

Here's my video about this new Laravel 10 feature.

Option 2. Laravel 9-

If you're on Laravel 9 or below, it's a bit longer syntax:

1. Change the ->get() at the end to ->toSql()
2. Add a separate sentence of dd($users);

$users = User::where('created_at', '<', now()->subYear())
    ->orderBy('email', 'asc')
    ->limit(5)
    ->toRawSql();
dd($users);