問題描述

我知道我可以在查詢構(gòu)建器上使用 toSql 方法來獲取帶有 SELECT 語句的綁定參數(shù)占位符的原始 SQL.

AppUser::where(['id'=>'1'])->toSql();

<塊引用>

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

但是我怎樣才能為 DELETE 語句得到這個(gè)?

AppUser::where(['id'=>'1'])->delete()->toSql();

<塊引用>

PHP 錯(cuò)誤:在第 1 行的整數(shù)上調(diào)用成員函數(shù) toSql()

這將執(zhí)行該語句，但我想獲得原始的、未插值的 SQL，其中問號(hào)代表值實(shí)際運(yùn)行查詢.同樣的情況適用于任何修改語句，例如 INSERT 或 UPDATE

但是為什么，誰在乎呢?

這很像xy問題.我的 Web 應(yīng)用程序包括一個(gè)多進(jìn)程架構(gòu).它在更新數(shù)據(jù)庫的事件驅(qū)動(dòng)循環(huán)中運(yùn)行帶有異步通信偵聽的自定義工匠命令.

我需要原始查詢的原因是因?yàn)槲蚁胫赜脺?zhǔn)備好的語句以提高性能.不幸的是，eloquent 方法沒有公開準(zhǔn)備好的語句，所以為了重用一個(gè)，我必須自己從底層 PDO 連接準(zhǔn)備它.

$sql = 'UPDATE `foo` SET `bar` = ?WHERE (`id` = ?)';$statement = DB::connection()->getPdo()->prepare($sql);而(真){$data = foo();$statement->execute([$data->bar, $data->id]);}

然而，這與抽象的 SQL 語法構(gòu)建器不同.因?yàn)槲夷壳罢谑褂?MySQL，所以我可以在語法上包含反引號(hào).但現(xiàn)在我陷入了供應(yīng)商鎖定的困境.例如，老板說我們明天要遷移到 MS SQL Server，那么(至少)必須捕獲使用反引號(hào)而不是方括號(hào)的錯(cuò)誤可能會(huì)很煩人.

我想使用動(dòng)態(tài)生成的 SQL 語法在準(zhǔn)備好的語句中重復(fù)使用.

首先獲取模型表的查詢構(gòu)建器實(shí)例.

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

然后獲取語法并使用 where 子句從構(gòu)建器編譯刪除語句.

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

<塊引用>

從 `users` 中刪除 `id` = ?"

現(xiàn)在您可以自由地更換數(shù)據(jù)庫驅(qū)動(dòng)程序，并且仍然可以獲得適當(dāng)?shù)钠脚_(tái)語法.

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.

AppUser::where(['id'=>'1'])->toSql();

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

But how can I get this for a DELETE statement?

AppUser::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.

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.

這篇關(guān)于如何獲取 Laravel 刪除/更新/插入語句的原始 SQL?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！