php - Converting SQL to Laravel Query Builder Error - Expert Level Required

Question

My SQL query wrote in PostgreSQL

SELECT date_trunc('day', created_at) AS hour_stamp,
      (extract(hour FROM created_at)::int / 60) AS min_slot, count(*),
      max(e4) as kwh
FROM energydata_1001
WHERE api_key_value= 'YaB8JCcE'
AND date(created_at) >= '2020-11-30 23:59:59'
AND date(created_at) <= '2020-12-16 00:00:00'
GROUP  BY 1, 2
ORDER  BY 1, 2;

I already wrote this query in Laravel Query Builder

$rnd = DB::table('energydata_1001')
            -> select (DB::raw("date_trunc('day',created_at) AS hour_stamp",
               '(extract(hour FROM created_at)::int / 60) AS min_slot'),
            DB::raw('count(*)'),
            DB::raw('max(e4) as kwh'))
            ->where ('api_key_value', '=', 'YaB8JCcE')
            ->where ('created_at', '>=', '2020-11-30 23:59:59')
            ->where ('created_at', '<=', '2020-12-29 00:00:00')
            ->groupBy('1','2')
            ->orderByRaw('1','2')
            ->get();

But I received an error like this when running this code.

When i ran this query in my PostgreSQL it works and is appear the data.

question from:https://stackoverflow.com/questions/65559844/converting-sql-to-laravel-query-builder-error-expert-level-required

Answer 1

Try this instead:

$rnd = DB::table('energydata_1001')->select(DB::raw('DATE_TRUNC('day', created_at) AS hour_stamp'));

/* select statements */
$rnd = $rnd->addSelect(DB::raw('(EXTRACT(hour FROM created_at)::int / 60) AS min_slot'));
$rnd = $rnd->addSelect(DB::raw('COUNT(*)'));
$rnd = $rnd->addSelect(DB::raw('MAX(e4) AS kwh'));

/* where clause */
$rnd = $rnd->where('api_key_value', 'YaB8JCcE');
$rnd = $rnd->where('created_at', '>=', '2020-11-30 23:59:59');
$rnd = $rnd->where('created_at', '<=', '2020-12-29 00:00:00');

$rnd = $rnd->groupBy(1, 2);
$rnd = $rnd->orderBy(1);
$rnd = $rnd->orderBy(2);
$rnd = $rnd->get();