Calculating value differences between two records in Eloquent

Question

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               |
----------------------------------

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)
----------------------------------

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.

Any ideas?

Answer 1

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 is a table with only one int column and 1M rows.

And The result is:

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

So the "small performance impact from eloquent" is:

• 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.

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.

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

However you can try a mixed version - Use Eloquent to build the query, but convert it to Database\Query\Builder 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:

Using session variables in an Eloquent query converted to Database\Query\Builder:

$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

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

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

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.