問題描述

我想用 eloquent 計算兩條記錄之間的差異.例如，我有下表:

I would like to calculate the difference between two records in eloquent. For example, I have following table:

----------------------------------
| Id | value | type              |
----------------------------------
| 1  | 100   | FOO               |
| 2  | 500   | FOO               |
| 3  | 800   | FOO               |
| 4  | 200   | BAR               |
| 5  | 600   | BAR               |
| 6  | 1000  | FOO               |
----------------------------------

假設模型名稱是 FooBar，所以當我過濾表格時，例如，使用 FooBar::where('type', 'FOO')->get(); 我將得到以下結果:

Let's say the model name is FooBar, so when I filter the table, for example, using FooBar::where('type', 'FOO')->get(); I will get following result:

----------------------------------
| Id | value | type  | diff      |
----------------------------------
| 1  | 100   | FOO   | 0         |
| 2  | 500   | FOO   | 400       | (500 - 100)
| 3  | 800   | FOO   | 300       | (800 - 500)
| 6  | 1000  | FOO   | 200       | (1000 - 800)
----------------------------------

現在，也許我可以使用原始查詢更輕松地實現這一點，例如聲明變量來存儲以前的記錄(例如:SET @id := 0 并在 SELECT 語句中設置它).但在這種情況下，如果可能，我更喜歡使用 Eloquent.

Now, probably I could achieve this more easily with raw query, like declaring variable to store previous record (e.g: SET @id := 0 and set it in SELECT statement). But in this case I prefer to use Eloquent if possible.

我目前的解決方法是循環結果集并手動計算，我擔心這會影響性能.

My current workaround is loop the result set and calculate manually which I'm afraid it will affect the performance.

有什么想法嗎?

推薦答案

我不介意 eloquent 對性能的小影響，但會循環通過計算差異的結果集.. 我的意思是，如果我有數千條記錄，一個一個循環是粗略的想法

I don't mind small performance impact from eloquent, but looping thru the result set to calculate difference.. I mean, cmon if I had thousands records, looping one by one is rough idea

那么我有一個驚喜給你 - 這是一個小的性能測試:

Then I have a surprise for you - Here is a small performance test:

class Seq extends Eloquent {
    protected $table = 'helper.seq';
    protected $primaryKey = 'i';
}

Route::get('/loop', function () {
    $limit = 10000;

    $st = microtime(true);
    $data = Seq::orderBy('i')->take($limit)->get();
    var_dump(microtime(true) - $st);

    $st = microtime(true);
    foreach ($data as $row) {
        $row->i;
    }
    var_dump(microtime(true) - $st);

    $pdo = DB::getPdo();
    $st = microtime(true);
    $data2 = $pdo
        ->query("select * from helper.seq order by i limit $limit")
        ->fetchAll(PDO::FETCH_OBJ);
    var_dump(microtime(true) - $st);

    $st = microtime(true);
    foreach ($data2 as $k => $row) {
        if ($k == 0) {
            $row->diff = 0;
        } else {
            $row->diff = $row->i - $data2[$k-1]->i;
        }
    }
    var_dump(microtime(true) - $st);
});

helper.seq 是一張只有一個 int 列和 100 萬行的表.

helper.seq is a table with only one int column and 1M rows.

結果是:

0.779045s <- Fetch from DB with Eloquent

1.022058s <- Read Eloquent data (Only one column and do nothing with it)

0.020002s <- Fetch from DB with PDO

0.009999s <- Calculate all diffs in a loop

所以eloquent 對性能的影響很小"是:

So the "small performance impact from eloquent" is:

• 從數據庫中獲取數據時，比使用普通 PDO 和 stdClass 慢近 20 倍.
• 在循環中讀取屬性/屬性時，至少比 stdClass 慢 100 倍.

• Almost 20 times slower than using plain PDO and stdClass when fetching data from database.
• At least 100 times slower than stdClass when reading properties/attributes in a loop.

因此，如果您想提高性能，請在處理大量數據時切換到普通 PDO，或者至少使用默認的 Builder.

So if you want to improve the peroformance, switch to plain PDO when dealing with big amounts of data or at least use the default Builder.

現在你仍然可以嘗試在 MySQL 中完成這項工作，但要求使用 Eloquent 是沒有意義的.

Now you can still try to do the job in MySQL, but the requirement to use Eloquent wouldn't make sence.

然而，您可以嘗試混合版本 - 使用 Eloquent 構建查詢，但使用 getQuery() 將其轉換為 DatabaseQueryBuilder.

However you can try a mixed version - Use Eloquent to build the query, but convert it to DatabaseQueryBuilder with getQuery().

$fooBars = FooBar::where('type', 'FOO')->orderBy('id')
    ->getQuery()
    ->select(['*', DB::raw('coalesce(`value` - @last, 0)'), DB::raw('@last := `value`')])
    ->get();

但我總是避免在應用程序代碼中以這種方式使用會話變量，因為我已經看到許多此類解決方案在版本升級后返回錯誤/意外的結果.

But I would always avoid using session variables this way in application code, because i've seen many of such solutions returning wrong/unexpected results after a version upgrade.

還是不相信?以下是一些其他測試:

Still not convinced? Here are some other tests:

在轉換為 DatabaseQueryBuilder 的 Eloquent 查詢中使用會話變量:

Using session variables in an Eloquent query converted to DatabaseQueryBuilder:

$st = microtime(true);
$data = Seq::getQuery()
    ->select(['*', DB::raw('coalesce(i - @last, 0)'), DB::raw('@last := i')])
    ->orderBy('i')->take($limit)->get();
var_dump(microtime(true) - $st);

// runtime: 0.045002s

使用轉換后的 Eloquent 查詢的 PHP 解決方案:

PHP solution using converted Eloquent query:

$st = microtime(true);
$data2 = Seq::getQuery()->orderBy('i')->take($limit)->get();
foreach ($data2 as $k => $row) {
    if ($k == 0) {
        $row->diff = 0;
    } else {
        $row->diff = $row->i - $data2[$k-1]->i;
    }
}
var_dump(microtime(true) - $st);

// runtime: 0.039002

帶有普通 PDO 和 stdClass

PHP solution with plain PDO and stdClass

$st = microtime(true);
$data3 = $pdo
    ->query("select * from helper.seq s1 order by i limit $limit")
    ->fetchAll(PDO::FETCH_OBJ);
foreach ($data3 as $k => $row) {
    if ($k == 0) {
        $row->diff = 0;
    } else {
        $row->diff = $row->i - $data3[$k-1]->i;
    }
}
var_dump(microtime(true) - $st);

// runtime: 0.035001s

帶有普通 PDO 和關聯數組的 PHP 解決方案:

PHP solution with plain PDO and assotiative arrays:

$st = microtime(true);
$data4 = $pdo
    ->query("select * from helper.seq s1 order by i limit $limit")
    ->fetchAll(PDO::FETCH_ASSOC);
foreach ($data4 as $k => $row) {
    if ($k == 0) {
        $row['diff'] = 0;
    } else {
        $row['diff'] = $row['i'] - $data4[$k-1]['i'];
    }
}
var_dump(microtime(true) - $st);

// runtime: 0.027001s

您首選的解決方案是最慢且最不可靠的.所以你的問題的答案對你的問題來說是一個糟糕的解決方案.

Your prefered solution is the slowest and the least reliable. So the answer to your question is a bad solution for your problem.

這篇關于在 Eloquent 中計算兩條記錄之間的值差異的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！