Count the number of records that have a given status applied to it in a specificed time period-CodePudding

I have three tables matters, statuses, and matter_status

A matter belongs to many statuses and statuses belongs to many matters.

What I want to do is count the number of matters each status has in a given time period. However, I need to alter that query in order to count by the latest status a matter has rather than all statuses it has had.

For example, a matter can have a status of 'New' then a status of 'Pending' on the same day, that matter should not be included in the new count because it has a later status of pending.

This is what I have so far, but it is not returning the expected result

Matter::select('id')
  ->with('statuses')
  
  ->whereHas('statuses', function ($query) {
    $query->where('statuses.id', 1)->whereMonth('statuses.created_at', Carbon::now()->month);
})
->latest()
->count();

| matter_id | status_id | created_at          |
|-----------|-----------|---------------------|
| 1         | 1         | 2022-06-30 12:58:47 |
| 2         | 1         | 2022-06-30 12:58:47 |
| 2         | 2         | 2022-06-30 14:12:36 |

The matter_id = 2 has two status_ids with one created after the other.

So when counting the number of matters that have a status_id of 1 the answer should be one and not two as I only want to count the latest entry.

So I have managed to get a query returning the correct result. However, it's non-performant... 2 seconds on a table containing 8,000 rows. Here is said query:

Matter::select('matters.*')
    ->join('matter_status', 'matters.id', 'matter_status.matter_id')
    ->where('matter_status.status_id', $this->status)
    ->whereBetween('matter_status.created_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()])
    ->where('matter_status.created_at', function ($query) {
        $query->selectRaw('max(created_at)')
            ->from('matter_status')
            ->whereColumn('matter_id', 'matters.id');
    })
    ->count();

CodePudding user response：

Your example query looks like it's trying to get the latest status each Matter has. For this, you can use latestOfMany() (Laravel 8.42 ):

// Matter.php
public function latestStatus()
{
    return $this->hasOne(Status::class)->latestOfMany();
}

This will return all matters along with their latest status:

Matter::select('id')
    ->with('latestStatus')
    ->latest()
    ->get();

However your example query is constraining when the Status itself was created, not when it got attached to the Matter. Assuming your pivot table has timestamps, you can constrain the pivot entry using one of the wherePivot methods.

Matter::select('id')
    ->with('latestStatus')
    ->whereHas('latestStatus', function ($status) {
        $status->wherePivotBetween('created_at', [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth]);
    })
    ->latest()
    ->get();

CodePudding user response：

I managed to get what I wanted with the following query

Matter::select('matters.*')
->join('matter_status', 'matters.id', 'matter_status.matter_id')
->where('matter_status.status_id', $this->status)
->whereBetween('matter_status.created_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()])
->where('matter_status.created_at',
    function ($query) { $query->selectRaw('max(created_at)')
->from('matter_status')->whereColumn('matter_id', 'matters.id'); })
->count();```