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

user_4233593 0
Viewed 15376

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.

eloquent laravel php
4 Answers

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.

user_45770620 
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

user_113848620
Related