問題描述
我想用 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 |
----------------------------------
假設(shè)模型名稱是 FooBar，所以當(dāng)我過濾表格時，例如，使用 FooBar::where('type', 'FOO')->get(); 我將得到以下結(jié)果:
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)
----------------------------------
現(xiàn)在，也許我可以使用原始查詢更輕松地實(shí)現(xiàn)這一點(diǎn)，例如聲明變量來存儲以前的記錄(例如:SET @id := 0 并在 SELECT 語句中設(shè)置它).但在這種情況下，如果可能，我更喜歡使用 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.
我目前的解決方法是循環(huán)結(jié)果集并手動計算，我擔(dān)心這會影響性能.
My current workaround is loop the result set and calculate manually which I'm afraid it will affect the performance.
有什么想法嗎?
推薦答案
我不介意 eloquent 對性能的小影響，但會循環(huán)通過計算差異的結(jié)果集.. 我的意思是，如果我有數(shù)千條記錄，一個一個循環(huán)是粗略的想法
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.
結(jié)果是:
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:
- 從數(shù)據(jù)庫中獲取數(shù)據(jù)時，比使用普通 PDO 和
stdClass慢近 20 倍. - 在循環(huán)中讀取屬性/屬性時，至少比
stdClass慢 100 倍.
- Almost 20 times slower than using plain PDO and
stdClasswhen fetching data from database. - At least 100 times slower than
stdClasswhen reading properties/attributes in a loop.
因此，如果您想提高性能，請在處理大量數(shù)據(jù)時切換到普通 PDO，或者至少使用默認(rèn)的 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.
現(xiàn)在你仍然可以嘗試在 MySQL 中完成這項(xiàng)工作，但要求使用 Eloquent 是沒有意義的.
Now you can still try to do the job in MySQL, but the requirement to use Eloquent wouldn't make sence.
然而，您可以嘗試混合版本 - 使用 Eloquent 構(gòu)建查詢，但使用 getQuery() 將其轉(zhuǎn)換為 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();
但我總是避免在應(yīng)用程序代碼中以這種方式使用會話變量，因?yàn)槲乙呀?jīng)看到許多此類解決方案在版本升級后返回錯誤/意外的結(jié)果.
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:
在轉(zhuǎn)換為 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
使用轉(zhuǎn)換后的 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 和關(guān)聯(lián)數(shù)組的 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.
這篇關(guān)于在 Eloquent 中計算兩條記錄之間的值差異的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！