Fill Gaps in Statistical Time Series - Date by the hour

mysql.php

$requests = DB::table('advert_requests as ar')
    ->select(DB::raw("date_format(ar.created_at, '%Y-%m-%d %H' ) AS grouped_hour, count(*) AS requests"))
    ->join('placements as p', 'p.id', '=', 'ar.placement_id')
    ->where('ar.created_at', '>=', $from)
    ->where('p.site_id', $site_id)
    ->groupBy('grouped_hour')
    ->get();

OK, so forgive my ignorance here...

It looks like there is a package to do something with CTE's but not dates/hours https://github.com/staudenmeir/laravel-cte

AI said below which doesn't work and I don't understand...

// Define the recursive CTE query
$recursiveCTE = DB::raw('WITH RECURSIVE cte AS (
    SELECT :from AS grouped_date, 0 AS grouped_hour
    UNION ALL
    SELECT cte.grouped_date, cte.grouped_hour + 1
    FROM cte
    WHERE cte.grouped_hour < 23
)');

$requests = DB::table('advert_requests as ar')
    ->select(DB::raw('cte.grouped_date, cte.grouped_hour, COUNT(ar.created_at) AS requests'))
    ->leftJoin('placements as p', 'p.id', '=', 'ar.placement_id')
    ->join(DB::raw('cte'), function ($join) {
        $join->on(DB::raw('DATE(ar.created_at)'), '=', DB::raw('cte.grouped_date'))
            ->on(DB::raw('HOUR(ar.created_at)'), '=', DB::raw('cte.grouped_hour'));
    })
    ->where('ar.created_at', '>=', DB::raw(':from'))
    ->where('p.site_id', DB::raw(':site_id'))
    ->groupBy('cte.grouped_date', 'cte.grouped_hour')
    ->orderBy('cte.grouped_date', 'asc')
    ->orderBy('cte.grouped_hour', 'asc')
    ->setBindings([
        'from' => $from,
        'site_id' => $site_id,
    ])
    ->get(['cte.grouped_date', 'cte.grouped_hour', 'requests']);

So should I just use RAW SQL? If the answer is yes, I'd like to pay for someone's time to help me get it right... anyone?

I really have no idea what I'm doing.

You can't build the SQL query like this because the WITH ... needs to proceed everything. staudenmeier/laravel-cte is good. I've implemented CTEs in my PG driver the same way like he did.

We can do a session again some time. Send me a DM on Twitter if you want to.