Eloquent multiple joins in laravel

Question

I have Three tables: 1) Orders 2) Users 3) Services

Orders table contains:- a) user_id b) service_id

User tables contains:- a) firstname b)lastname c) email

Service table contains: a) service_name

What is happening is that a service provider on website is adding his services and these services are getting saved in service table. Whereas, the users book these services and after booking it's user_id and the booked service_id gets saved in orders table.

What i have done is the logged in service provider can only see those users details who are booked in orders table.

For this, i used below code, using eloquent:-

$get_result = App\Order::whereIn('user_id',$user_id)->whereIn('service_id',$service_id)->get();

where $user_id contains the user ids and $service_id contains the service ids. This gives the result of those users who are registered for the logged in provider's services.

Now, the issue i am facing is I want to fetch the user details like firstname,lastname,email from users table and service name from service table.

How should i join these tables in eloquent?

Answer 1

Now, the issue I am facing is I want to fetch the user details like firstname, lastname, email from users table and service name from service table.

Use eager loading. Add this clause to your query:

->with(['service', 'user'])

This will load Service and User models for every Order.

Make sure, you've defined relationships in the Order model:

public function user()
{
    return $this->belongsTo(User::class);
}

public function service()
{
    return $this->belongsTo(Service::class);
}

Answer 2

From your existing query, using a left Join you can extend it to

$get_result = App\Order::whereIn('user_id',$user_id)
->whereIn('service_id',$service_id)
->leftJoin('users', 'orders.user_id', 'users.id')
->leftJoin('services', 'orders.service_id', 'services.id')->get();

and if you want to select specific columns, you can add

->select('orders.*', 'users.firstname', 'users.lastname', 'users.email', 'services.name')

If you don't want to go this route with the query, you can also use eager loading, which would add the Service and User models to your order request as explained here https://laravel.com/docs/5.5/eloquent-relationships#eager-loading