-
-
Save jamesmills/91f3056dc1a9f39ed10273497a7fe8a5 to your computer and use it in GitHub Desktop.
$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(); |
https://twitter.com/jamesmills/status/1677290025476661248
I joked about a "query as a service" for this sort of thing. Tobias pointed out this is what consultancy is. So if you want to write this for me I'll pay for your time on a screen share to get this done. DM me.
Thanks.
You need CTEs for this, but they are not available in Eloquent. Or you need the laravel-cte package.
@tpetry is correct. The other option is to fill a table with every hour in the year and then join it. Kinda gross, but would work. It's only 87,600 rows to cover ten years, so it's barely any data at all.
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.
I'm looking to plot hourly data on a chart. The above gets me what I want but I'd like to pad the missing hours and missing dates.
I'm assuming this is the right base query.
I'm using Laravel Eloquent format because I'll be adapting this to use
->when($site_id, function ($query) {
later to filter things.