Search code examples
javascriptphpajaxlaraveldatatable

Joins queries to get multiple data to datatable in laravel

I want to display some multiple values in a one column of the datatable. I have joined two tables to show the values in datatable. It includes customer data, and customer has multiple services that data gets from a another join query. how can i combine these together to display multiple service values according to each customer in datatable. I'm new to laravel and appreciate your help!

Join query to pass data to datatable without multiple values:

    public function getAppointmentData(Request $request)
    {

        $getData = DB::table('customers')
        ->join('jobs', 'jobs.id', '=', 'customers.id')
        ->select('jobs.id', 'user_type','firstname','lastname', 'vehiclemodel', 'date', 'time', 'payment_status', 'amount')->get();

        $datatable = DataTables::of($getData)->make(true);
        return $datatable;
    }

get multiple service values according to each customer:

        $getData = DB::table('customers')
        ->join('customer_service', 'customer_service.customer_id', '=', 'customers.id')
        ->join('services_info', 'services_info.id', '=', 'customer_service.id')
        ->select('customer_id','service_id','service')
        ->get();

Output of above code:

enter image description here

Datatable i already have:

enter image description here

I want a service column in datatable to show multiple service values according to each customer.

Solution

  • You could use MySQL's GROUP_CONCAT()

    The function:

    public function getAppointmentData(Request $request)
{
    $services = DB::table('customers')
        ->join('customer_service', 'customer_service.customer_id', '=', 'customers.id')
        ->join('services_info', 'services_info.id', '=', 'customer_service.id')
        ->selectRaw("
         customer_id,
         GROUP_CONCAT(service) as services_list
        ")
        ->groupBy('customer_id')
        ->toSql();

    $getData = DB::table('customers')
    ->join('jobs', 'jobs.id', '=', 'customers.id')
    ->join(DB::raw("({$services} as services)"), 'services.customer_id', '=', 'customers.customer_id')
    ->select('jobs.id', 'user_type','firstname','lastname', 'vehiclemodel', 'date', 'time', 'payment_status', 'amount', 'services_list')->get();

    $datatable = DataTables::of($getData)->make(true);
    return $datatable;
}