trying to get all values from groupBy

Hello Guys,

PROBLEM DEFINITION - i have 3 tables, namely - waitlists, waitlist_services, waitlist_service_times. I am joining these 3 tables like this -

$waitlist = Waitlist::with('waitlist_services')->select('waitlist_service_times.day', 'employee_sub_categories.id as emp_sub_cat_id', 'waitlist_services.service_request_id', 'waitlist_service_times.waitlist_services_id', 'waitlist_services.id as waitlist_ser_id', 'waitlist_services.waitlist_id' ,'waitlists.id', 'waitlists.date_joined', 'waitlists.priority', 'client_intakes.first_name', 'client_intakes.last_name', 'client_intakes.client_dob', 'waitlist_service_times.start_time','waitlist_service_times.end_time','waitlist_service_times.or_start_time','waitlist_service_times.or_end_time', 'employee_sub_categories.sub_cat_name')
            ->join('client_intakes', 'client_intakes.id', 'waitlists.client_intakes_id')
            ->join('waitlist_services', 'waitlists.id', 'waitlist_services.waitlist_id')
            ->join('waitlist_service_times', 'waitlist_services.id', 'waitlist_service_times.waitlist_services_id')
            ->join('employee_sub_categories', 'employee_sub_categories.id', 'waitlist_services.service_request_id');

$waitlist = $waitlist->groupBy('waitlists.id');
$waitlist = $waitlist->get();

CURRENT OUTPUT - if remove line $waitlist = $waitlist->groupBy('waitlists.id');

| waitlist_id | service_request_id's | day's |

| 1 | 1 | Monday |

| 1 | 2 | Tuesday |

EXPECTED OUTPUT -

| waitlist_id | service_request_id's | day's |

| 1 | 1,2 | Monday, Tuesday |

RELATIONS- waitlists has many waitlist_services , waitlist_services has many waitlist_service_times.

Any help would be appreciated.

SilenceBringer

5 years ago

Best Answer

Level 55

@amit028 look at mysql group_concat. So, your query should looks like

$waitlist = Waitlist::select(
        'waitlist.id',
        \DB::raw('group_concat(waitlist_services.service_request_id separator ", ") as service_request_ids')
    )
    ->join('waitlist_services', 'waitlists.id', 'waitlist_services.waitlist_id')
    ->groupBy('waitlists.id')
    ->get();

and similar things for other relations

amit028

5 years ago

Level 1

Thank you @silencebringer

It worked. :)

Please or to participate in this conversation.