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-18 08:39:32Z

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, 2019 at 8:39

answered Dec 18, 2019 at 7:54

KiprasT

3955 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Hashan Over a year ago

how can i display that concatenate text as a list in one datatable column?

KiprasT Over a year ago

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

Hashan Over a year ago

i want to combine above code output with the join, in getAppointmentData method to pass select query to show in datatable

KiprasT Over a year ago

Then use $getData as a sub-query. Substitute get() to toSql() and add it like this: ->join(DB::raw($getData), ...)

KiprasT Over a year ago

I've edited my answer, but as of now I can't tell you if there are no syntax errors.

|

Iamnot Guy Vawkes · Accepted Answer · 2022-08-08 02:04:08Z

0

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

edited Aug 8, 2022 at 2:04

answered Aug 2, 2022 at 6:41

Iamnot Guy Vawkes

11 bronze badge

1 Comment

Abdulla Nilam Over a year ago

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

Collectives™ on Stack Overflow

Joins queries to get multiple data to datatable in laravel

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related