I have the following select
$repairs = DB::select(DB::raw('
select r.id repair_id, w.name, w.surname
from repairs r
join repair_worker rw on r.id = rw.repair_id
join workers w on w.id = rw.worker_id
where r.vehicle_id = ?
group by w.name, w.surname, r.id
'),[$vehicle->id]);
and I am trying to get w.name and w.surname into one field, separated by a comma for example. There is a function string_agg(column, ',') but I am having no luck to get it working. I always get syntax error.
My try:
$repairs = DB::select(DB::raw('
select r.id repair_id, string_agg(w.name, w.surname, ', ') workers
from repairs r
join repair_worker rw on r.id = rw.repair_id
join workers w on w.id = rw.worker_id
where r.vehicle_id = ?
group by r.id
'),[$vehicle->id]);
How would you do it?
I am using the newest Postgresql 9+
Use the || operator to perform string concatenation, then aggregate this result.
$repairs = DB::select(DB::raw("
select r.id repair_id, string_agg(w.name || ' ' || w.surname, ', ') workers
from repairs r
join repair_worker rw on r.id = rw.repair_id
join workers w on w.id = rw.worker_id
where r.vehicle_id = ?
group by r.id
"),[$vehicle->id]);
try this?
$repairs = DB::select(DB::raw("
select r.id repair_id, string_agg(concat(w.name, ',',w.surname), ', ') workers
from repairs r
join repair_worker rw on r.id = rw.repair_id
join workers w on w.id = rw.worker_id
where r.vehicle_id = ?
group by r.id
"),[$vehicle->id]);