Joins queries to get multiple data to datatable in laravel

Question

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:

Datatable i already have:

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

KiprasT · Accepted Answer · 2019-12-18T08:39:32.067

1

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;
}

edited Dec 18 '19 at 08:39

answered Dec 18 '19 at 07:54

KiprasT

349
5
13

how can i display that concatenate text as a list in one datatable column? – Hashan Dec 18 '19 at 08:06
By default - MySQL will concatenate strings with a separator: `,`. You can change it to almost whatever character you want. Then it's a matter of parsing it in the front-end, for that you could use [JavaScript's split](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/split) – KiprasT Dec 18 '19 at 08:13
i want to combine above code output with the join, in getAppointmentData method to pass select query to show in datatable – Hashan Dec 18 '19 at 08:19
Then use `$getData` as a [sub-query](https://stackoverflow.com/a/18120728/6463262). Substitute `get()` to `toSql()` and add it like this: `->join(DB::raw($getData), ...)` – KiprasT Dec 18 '19 at 08:26
I've edited my answer, but as of now I can't tell you if there are no syntax errors. – KiprasT Dec 18 '19 at 08:40
it gives this error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version f ▶ customer_id, GROUP_CONCAT(service) as services_list from `customers` inner join `customer_service` on `customer_service`.`customer_id` = `customers`.`id` inner join `services_info` on `services_info`.`id ▶ – Hashan Dec 18 '19 at 08:55
Instead of displaying multiple values in one column, is it possible to add a custom button to service field, when click the button it will show the multiple values in a bootstrap modal. how can i add custom button to perform this. – Hashan Dec 18 '19 at 08:58

Joins queries to get multiple data to datatable in laravel

1 Answers1