• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

149
Views
String_agg for multiple columns

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+

about 3 years ago · Santiago Trujillo
2 answers
Answer question

0

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]);
about 3 years ago · Santiago Trujillo Report

0

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]);
about 3 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error