Queries in Laravel – Equivalent to left join

Currently this is my laravel query

    $response['appointMent'] = $this->appointment->where('user_id', Auth::guard('user')->user()->id)
    ->whereIn('status', array('Sched', 'Going', 'Request Appointment'))
    ->whereDate('set_date', '>', Carbon::now()->subDays(1))
    ->with(['company'])
    ->with(['applicant'])
    ->with(['job'])
    ->with(['user'])->get();

But unfortunately, I would also like to get the connected data between the company and the user table, user_id for company and id for user

this is the table company table:

and the user table

How to I connect the user under the company array?

Answer

You need to make a relationship for the company with the user like so

Company Model

public function user()
{
   return $this->belongsTo(Company::class,'user_id');
}

And your query will become

response['appointMent'] = $this->appointment->where('user_id', Auth::guard('user')->user()->id)
                ->whereIn('status', array('Sched', 'Going', 'Request Appointment'))
                ->whereDate('set_date', '>', Carbon::now()->subDays(1))
                ->with(['company.user','applicant','job'])->get();

Now the user relationship will be inside company

OR the reverse will be User Model

public function company()
{
   return $this->hasOne(User::class); //Or hasMany depends on your needs
}

Then the following query will be changed to

response['appointMent'] = $this->appointment->where('user_id', Auth::guard('user')->user()->id)
                ->whereIn('status', array('Sched', 'Going', 'Request Appointment'))
                ->whereDate('set_date', '>', Carbon::now()->subDays(1))
                ->with(['user.company','applicant','job'])->get();