How to get the raw SQL for a Laravel delete/update/insert statement?

Question

I know I can use the toSql method on a query builder to get the raw SQL with binding parameter placeholders for a SELECT statement.

App\User::where(['id'=>'1'])->toSql();

"select * from `users` where (`id` = ?)"

But how can I get this for a DELETE statement?

App\User::where(['id'=>'1'])->delete()->toSql();

PHP error: Call to a member function toSql() on integer on line 1

This executes the statement, but I would like to get the raw, uninterpolated SQL generated with the question marks standing in for values without actually running the query. The same case holds for any modification statement, such as INSERT or UPDATE

But why, who cares?

This smells a lot like an xy problem. My web application includes a multi-process architecture. It runs custom artisan commands with asynchronous communication listening in an event-driven loop that update the database.

The reason I need the raw query is because I want to reuse a prepared statement for performance efficiency. Unfortunately, the eloquent methods do not expose the prepared statement, so in order to reuse one, I'll have to prepare it myself from the underlying PDO connection.

$sql = 'UPDATE `foo` SET `bar` = ? WHERE (`id` = ?)';

$statement = DB::connection()->getPdo()->prepare($sql);

while (true) {
    $data = foo();
    $statement->execute([$data->bar, $data->id]);
}

However, this departs from the abstracted SQL grammar builder. Because I'm using MySQL at the moment, I can syntactically include the backticks. But now I'm stuck with vendor lock-in. Say for example, the boss says we're moving to MS SQL Server tomorrow, then it's likely going to be annoying (at least) to have to catch bugs for using backticks instead of square braces.

I want to use the dynamically generated SQL grammar for reusing in a prepared statement.

possible workaround https://laravel-news.com/quickly-dumping-laravel-queries — haakym, Jul 19 '17 at 14:33
You could also take a look at the laravel-debugbar package which logs all queries. I think you should just run `toSql()` without the delete and assume instead of select it will run delete. — haakym, Jul 19 '17 at 14:39
Not 100% but I think `delete()` actually executes the query, much like `get()` executes it and returns the result. What are you trying to achieve with it? — Jonathon, Jul 19 '17 at 14:46
Not 100% sure, but does the Laravel DebugBar (https://github.com/barryvdh/laravel-debugbar) give the SQL statements from a page? — Nigel Ren, Jul 19 '17 at 14:48
@Jonathon that's why I said to use `toSql()` then assume it will replace the select part of the query. — haakym, Jul 19 '17 at 14:58
@NigelRen yes it does show queries, and even optimisations for those queries! — haakym, Jul 19 '17 at 14:59

score 14 · Accepted Answer · answered Jul 20 '17 at 19:53

14

First get a query builder instance for the model's table.

$builder = DB::table((new User)->getTable());

Then get the grammar and compile the delete statement from the builder with a where clause.

$sql = $builder->getGrammar()->compileDelete($builder->where('id', 1));

"delete from `users` where `id` = ?"

Now you can freely swap out database drivers and still get the appropriate platform syntax.

answered Jul 20 '17 at 19:53

Jeff Puckett

37,464
17
118
167

another hackish options is to put delete statement in transaction and to rollback it after – Tebe Dec 28 '17 at 08:50
@Jeff Puckett Maybe you can help me. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent – moses toh Aug 14 '18 at 10:28

score 10 · Answer 2 · answered Jul 19 '17 at 14:46

10

You can do something like this:

DB::enableQueryLog();
App\User::where(['id'=>'1'])->delete();
dd(DB::getQueryLog());

answered Jul 19 '17 at 14:46

Sazzadur Rahman

2,650
1
18
34

2

Thanks, this is helpful, but unfortunately this executes the query. I need the statement beforehand. – Jeff Puckett Jul 19 '17 at 21:32

score 2 · Answer 3 · answered Oct 04 '18 at 15:35

This is what I did. I created a macro toDeleteSql() on the query builder.

\Illuminate\Database\Query\Builder::macro('toDeleteSql', function($func) {
    return [
        'sql' => $this->getGrammar()->compileDelete(with($this, $func)),
        'bindings' => $this->getBindings()
    ];
});

Now when I query, I can do something like this:

$user = \DB::table('user')
    ->toDeleteSql(function($query) {
        return $query
            ->join( 'photo', function($join) {
                $join->on('user.user_id', '=', 'photo.user_id');
                $join->where('photo.type','avatar');
            })
            ->where('user_id',123)
            ->whereNull('deleted_at');
    });

And now user is an array with both your sql and your bindings so you can do what you want with it. In addition you could also create the macro on the eloquent query builder for work with models.

score -2 · Answer 4 · answered Mar 14 '20 at 06:19

-2

public function index()
    {
        $users = DB::select('select * from users where active = ?', [1]);

        return view('user.index', ['users' => $users]);
    }

use delete like this $deleted = DB::delete('delete from users');

for update $affected = DB::update('update users set votes = 100 where name = ?', ['John']);

references https://laravel.com/docs/5.8/database#running-queries

answered Mar 14 '20 at 06:19

Saadique

1

Sorry I wanted to see what was rendered from the ORM grammar query compiler, not *how* to write the equivalent SQL. Thanks anyway – Jeff Puckett Mar 14 '20 at 20:29

How to get the raw SQL for a Laravel delete/update/insert statement?

But why, who cares?

4 Answers4

Linked