Getting 2 lots of relationship data from a Laravel collection with Yajra Datatables

Question

I am trying to get my Yajra Datatable working correctly but struggling.

Basically I want to get all clients appointments in which the client belongs to the logged in user. I then want to access the Client name and then the appointment data. I have used the following to get the appointment data

$user = User::find(Auth::user()->id);

$data = $user->clients()->with('appointments')->get()->pluck('appointments')->flatten();

return Datatables::of($data)
    ->make(true);

This allows me to show a row for each appointment that exists for the user-owned clients appointments. But how can I also access the clients name from this?

I have tried many different ways but if I use something like

$data = $user->clients()->with('appointments')->get();

I can access all of the data I need but it shows the existing clients in each row, not all appointments owned by the clients.

My setup is

User Model

    public function clients(){

        return $this->hasMany(Client::class);

    }

    public function appointments()
    {
        return $this->hasManyThrough(Appointment::class, Client::class);
    }

Client Model

   public function appointments(){

        return $this->hasMany(Appointment::class);

    }

    public function users(){

        return $this->belongsTo(User::class);

    }

Appointment Model

 public function client(){

        return $this->belongsTo(Client::class);

    }

Thank you

Answer 1

You might be looking for whereHas:

$appointments = Appointment::with('client')
    ->whereHas('client.users', function ($query) use ($user) {
        $query->where('users.id', $user->id)
    })
    ->get();

This translates to: "Give me all the appointments that belong to clients of a specific user".

Just a suggestion, it might be wise (not required at all) to alter your database structure to something more flexible. What if you for instance keep track in you appointment which user is present?

appointments
- user_id
- client_id

This would make your query a bit simpler

// In User
public function appointments()
{
    return $this->hasMany(Appointment::class);
}

$user->appointments()->with('client')->get();

You could go even further by making the relation between User and Appointment, Client and Appointment many to many so you could have appointments with more than one user and/or client.