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

Question

I know that I can use the toSql method in the query builder to get the original SQL with binding parameters binding for the SELECT .

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

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

But how can I get this for a DELETE ?

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

PHP error: calling toSql () member function for an integer on line 1

This statement executes, but I would like to get unprocessed, uninterpreted SQL created with question marks standing for values ​​without actually executing the query. The same case applies to any modification statement, such as INSERT or UPDATE

But why, who cares?

This smells a lot like xy . My web application includes a multiprocessor architecture. It runs custom asynchronous wizard commands, listening to an event-driven loop that updates the database.

The reason I need a raw request is because I want to reuse a prepared statement to improve efficiency. Unfortunately, eloquent methods do not expose a prepared statement, so for its reuse, I have to prepare it myself from a basic 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 abstract SQL grammar constructor. Since I'm currently using MySQL, I can syntactically include backlinks. But now I am stuck with a vendor lock. Say, for example, the boss says that tomorrow we will switch to MS SQL Server, and then it will probably be annoying (at least) to catch errors for using reverse loops instead of square brackets.

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

Answer 1

First create an instance of the query builder for the model table.

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

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

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

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

Now you can freely change the database drivers and get the appropriate platform syntax.

Answer 2

You can do something like this:

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