php – How to get the raw SQL for a Laravel delete/update/insert statement?-ThrowExceptions

Exception or error:

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.

How to solve:

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.

Answer:

You can do something like this:

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

Answer:

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.

Answer:

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

Leave a Reply

Your email address will not be published. Required fields are marked *