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

370
5
14

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

Iamnot Guy Vawkes · Answer 2 · 2022-08-08T02:04:08.853

The Join command in SQL (Structure Query Language) serves as a liaison between two or more tables that are related to each other.

By using this JOIN we can also display data from several tables by looking at the values that have similarities between tables even though the entities in the tables are not related to each other.

Create Relationship Commands in Category and Article Models We will connect the categories and articles tables using eloquent which we will later create to display data in Controllers.

Define relationship with the Artikel

    public function artikel()
    {
        return $this->hasMany(Artikel::class);
    }

Then add the following script in the Articles model:

    public function category(){
        return $this->belongsTo(Category::class);
    }

call the article model and the relation with the category model

$article = Article::with('category')->get();

Read Article

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/32388556) — Abdulla Nilam, Aug 06 '22 at 16:30

Joins queries to get multiple data to datatable in laravel

2 Answers2