CODEWITHSUNDEEP
laravel-5.7
user8990420
user8990420

Reputation:

Getting data from multiple tables in Laravel

I would like to get the details about a booking, the details are the service booked for the cost of each service, tax charged and the total amount, and the status

The cost, tax and total amount are from the Charge model, the status from the Status model, the service name from the Service model and the booking id and address from the Booking model.

Using the code below in my controller I am able to get the service name, the booking Id and the status but I am only getting one cost and tax and amount for all bookings despite that being unique in their respective tables.

    public function viewInvoices() {
$allProducts = Booking::get()->where('client', Auth::user()->name);
$products = json_decode(json_encode($allProducts));
foreach ($products as $key => $val) {
    $service_name = Service::where(['id' => $val->service])->first();
    $service_fee = Charge::where(['id' => $val->charge])->first();
    $service_status = Status::where(['id' => $val->status])->first();
    $products[$key]->service_name = $service_name->name;
    $products[$key]->service_fee = $service_fee->total;
    $products[$key]->service_status = $service_status->name;
    $product_charge = Charge::where(['id' => $val->charge])->get();
}$charges = json_decode(json_encode($product_charge));
return view('client.payment.invoices')->with(compact('products', 'charges'));
    }

That is what I have in the controller and in the view I am using a for each loop twice first to loop through $products then to loop through $charges.

Could I be doing something wrong and how can I rectify it?

Upvotes: 1

Views: 87

Answers (1)

The Only Smart Boy
The Only Smart Boy

Reputation: 574

I would suggest using joins to get data from more than one table, you would thus want to do it like this,

    public function viewInvoices() {
    $products = DB::table('bookings')
            ->join('services', 'bookings.service', '=', 'services.id')
            ->join('charges', 'bookings.charge', '=', 'charges.id')
            ->join('statuses', 'bookings.status', '=', 'statuses.id')
            ->select('bookings.*', 'services.s_name', 'services.description', 'statuses.name', 'charges.amount', 'charges.tax', 'charges.total')
            ->get()
            ->where('client', Auth::user()->name);
            return view('client.payment.invoices')->with(compact('products'));
 }

I have just Assumed your column names in each table based on your explanation therefore if they happen to be different just change appropriately.

Upvotes: 1

Related Questions