Laravel Database: Query Builder with SQL unnest()

Viewed 19

In my PSQL table "backend.measurement" I have the task to build a query with unnest:

SELECT m.channel, m.msrfile as measurement, iteration.timestamp as iteration 
FROM backend.measurement as m, unnest(m.iteration_times) WITH ORDINALITY iteration(timestamp, id) 
WHERE m.project_id = 1 LIMIT 10

I tried it with fromSub:

        $result = Measurement::select(['channel', 'msrfile as measurement', DB::raw('iteration.timestamp as iteration')])
        ->fromSub(function ($query) {
            $query->select('unnest(iteration_times) WITH ORDINALITY iteration(timestamp, id)')
                ->from('measurement');
        }, 'iteration')
        ->where('project_id', $project_id)->get();
0 Answers
Related