Get the difference of the same column of the same table, being the current value and that of the immediately previous date in the same query? – Mysql

I’ve gone through the possible related questions, but I haven’t found anything about it, please, if you see that I’m wrong and there’s something similar, let me know and I’ll delete this question if the rules say so to avoid duplication. thank you.

I’m stuck and I can’t get what I want in Laravel with eloquent, I hope you can help me or guide me to solve it, I didn’t really know how to ask the question, I will explain it in more detail and an example to make it as clear as possible.

I have divided the question into two parts, the first one is because I want to obtain the query grouped by dates and the second one, because I want to obtain the same one, but without grouping by date and specifying a specific date.

Thank you very much in advance and I hope you can help me, anything you need to help me solve this, do not hesitate to ask me, regards.

First part of the question

What I want is to get everything in the same eloquent query, without processing it afterwards.

I have the following table:

Schema::create('readings', function (Blueprint $table) {
    $table->id();
    $table->foreignId('counter_id')
        ->constrained()
        ->cascadeOnUpdate()
        ->restrictOnDelete();
    $table->date('date');
    $table->unsignedInteger('value');
});

The following is stored in the table (example):

And I want to get the following grouped (example):

? This is zero because there are no previous readings with which to make the difference.

I’ve tried to do it with mysql and I think that’s what I want, but I can’t get it to speak to Laravel:

SELECT r_current.date,
       count(r_current.id) AS readings_in_group,
       SUM(r_current.value -
           (
                SELECT value
                FROM readings
                WHERE r_current.counter_id = counter_id
                  AND r_current.date > date
                ORDER BY date DESC
                LIMIT 1
           )
       ) AS difference
FROM `readings` AS r_current
GROUP by r_current.date
;

First part of the question solved. Thank you.

The first part was solved with the first part of the partone() method of the example given by @Masunulla.

I passed the query I had in MySQL to Eloquent:

$subQuery = 'SELECT value FROM readings 
                        WHERE r_current.counter_id = counter_id 
                        AND r_current.date > date 
                        ORDER BY date DESC 
                        LIMIT 1';

$readings = Reading::from('readings AS r_current')
    ->select('r_current.date',
        DB::raw('COUNT(r_current.id) AS readings_in_group'),
        DB::raw('SUM(r_current.value - ('.$subQuery.')) AS total_difference')
    )
    ->groupBy('cr_current.date')
    ->get();

Second part of the question

What I want is to get everything in the same eloquent query, without processing it afterwards.

The second related part is that I want to obtain the same thing without grouping indicating a specific date, I have achieved this with a query in a looper, but I would like to optimize it, but the same thing happens to me as above, I don’t know how to do it in a query.

For example, for the date 2022-12-1 I want to get this:

For example, for the date 2022-11-1 I want to get this:

For example, for the date 2022-10-1 I want to get this:

? This is zero and null because there are no previous readings with which to make the difference.

I’ve managed to do this part using an additional query in foreach, but I’d like to have it all in one query, as it does a lot of queries and isn’t optimal.

This is what I do now:

IN CONTROLLER

 $query = Reading::query();
              ->with('counter');
              ->where('date', '=', $this->date) //Example 
              ->get();

IN MODEL

public function previous(): Model|null
{
    return $this->query()
        ->where('counter_id', '=', $this->counter_id)
        ->whereDate('date', '<', Carbon::parse($this->date)->format('Y-m-d'))
        ->orderBy('date', 'desc')
        ->first();
}

public function difference(): int
{
    $previous = $this->previous();
    
    if ($previous) {
        $difference = $this->value - $previous->value;
    } else {
        $difference = 0;
    }

    return $difference;
}

IN BLADE

@foreach($readings as $reading)
...
$reading->date
$reading->value
$reading->previous()->date
$reading->previous()->value
$reading->difference()
...
@endforeach

Second part of the question solved. Thank you.

Based on what I did in the first part, I tried to do something similar and this is the result, it does exactly what I want and although I don’t know if it is the most optimal way, the response is quite fast. If anyone comes up with something better, you’re more than welcome to reply. Thank you so much.

$concatSubQuery = 'FROM readings 
                    WHERE r_current.counter_id = counter_id 
                    AND r_current.date > date 
                    ORDER BY date DESC 
                    LIMIT 1';

$query = Reading::from('readings AS r_current')
    ->select('r_current.id', 'r_current.counter_id', 'r_current.date', 'r_current.value',
        DB::raw('(SELECT date '.$concatSubQuery.')  AS previous_date'),
        DB::raw('(SELECT value '.$concatSubQuery.')  AS previous_value'),
        DB::raw('SUM(r_current.value - (SELECT value '.$concatSubQuery.')) AS difference')
    )
    ->with('counter');
    ->where('date', '=', $this->date);
    ->groupBy('r_current.id', 'r_current.counter_id', 'r_current.date', 'r_current.value');
    ->get();