How to count rows in a table that will count as one when a specific condition meets in Laravel

I have a table named 'reports' with columns 'report_name,' 'zone,' and 'created_at.' I want to count all the rows where a row shares the same values for 'report_name' and 'zone,' and the 'created_at' column has a 30-minute interval compared to other rows. If multiple rows meet these criteria, they will be counted as one.

So far this is my code and I don't know how to query the row that will count as one when the 30 minutes interval in created_at column meets with other rows

$reports = Report::select('zone', 'report_name', DB::raw('COUNT(*) as count'))->groupBy('zone', 'report_name')->get();

tangtang

2 years ago

Level 6

@furukuku

try this for reference

$reports = Report::select('zone', 'report_name')
    ->selectRaw('COUNT(*) as count')
    ->groupBy('zone', 'report_name')
    ->havingRaw('COUNT(*) = 1') 
    ->orWhere(function ($query) {
        $query->selectRaw('COUNT(*)')
            ->from('reports as sub')
            ->whereRaw('reports.zone = sub.zone')
            ->whereRaw('reports.report_name = sub.report_name')
            ->whereRaw('TIMESTAMPDIFF(MINUTE, reports.created_at, sub.created_at) <= 30')
            ->whereRaw('TIMESTAMPDIFF(MINUTE, reports.created_at, sub.created_at) >= -30');
    })
    ->get();

the table row

| zone | report_name | created_at           |
|------|-------------|----------------------|
| A    | Report1     | 2023-10-19 10:00:00 |
| A    | Report1     | 2023-10-19 10:15:00 |
| B    | Report2     | 2023-10-19 11:00:00 |
| B    | Report2     | 2023-10-19 11:45:00 |
| C    | Report1     | 2023-10-19 12:00:00 |

result

| zone | report_name | count |
|------|-------------|-------|
| A    | Report1     | 2     |
| B    | Report2     | 2     |
| C    | Report1     | 1     |


updated :

but this code still count if the created at is like this with same zone and report name

| zone | report_name | created_at           |
|------|-------------|----------------------|
| A    | Report1     | 2023-10-19 10:00:00 |
| A    | Report1     | 2023-10-19 10:15:00 |
| A    | Report2     | 2023-10-19 11:30:00 |
| A    | Report2     | 2023-10-19 11:45:00 |

result is

| zone | report_name | count |
|------|-------------|-------|
| A    | Report1     | 4     |

because each row is in interval 30 minutes.

so to make this will exacly 30 minute, you must provided the minutes from what minutes and day to be compared with created_at.

e.g you want report from date 2023-10-19 and time 11:30:00. so do the query to compare all the report based on date 2023-10-19 and time interval from 11:30 to 12:00 (30 minutes after 11:30)

furukuku

2 years ago

Level 1

@tangtang I got this error upon trying the code you provided

Column not found: 1054 Unknown column 'sub.zone' in 'where clause'

tangtang

2 years ago

Level 6

@furukuku nah not fully tested before, and seems like there some query not work in laravel. this is the new code

$reports = DB::table('reports as report')
            ->select('report.zone', 'report.report_name', DB::raw('COUNT(*) as count'))
            ->where(function ($query) {
                $query->selectRaw('COUNT(*)')
                    ->from('reports as sub')
                    ->whereColumn('report.zone', 'sub.zone')
                    ->whereColumn('report.report_name', 'sub.report_name')
                    ->whereRaw('TIMESTAMPDIFF(MINUTE, report.created_at, sub.created_at) <= 30')
                    ->whereRaw('TIMESTAMPDIFF(MINUTE, report.created_at, sub.created_at) >= -30');
            }, 1)
            ->groupBy('report.zone', 'report.report_name')
            ->get();

        dd($reports);

but remember you need to provided the minutes from what minutes and day to be compared with created_at. (like what I say from the response before)

e.g you want report from date 2023-10-19 and time 11:30:00. so do the query to compare all the report based on date 2023-10-19 and time interval from 11:30 to 12:00 (30 minutes after 11:30)

becauce the code still not accurate to do the job without parameter.

Please or to participate in this conversation.