I have the UserModel which contains this relation:

public function patients()
{
    return $this->belongsToMany(User::class, 'invites', 'tenant_id', 'user_id');
}

This relation returns all the patients associated to the current logged in user, so if I call this:

auth()->user()->patients()->toSql();

it returns this query:

select
    *
from
    `users`
inner join
    `invites` on `users`.`id` = `invites`.`user_id`
where
    `invites`.`tenant_id` = ?
    and `users`.`deleted_at` is null

which is correct. But I would like to use the model like that:

$userModel = new App\Models\Boilerplate\User();
return $userModel::with('patients')->select([
    'users.id',
    'email',
    'last_name',
    'first_name',
    'active',
    'users.created_at',
    'last_login',
]);

if I print the last query, I get this:

select
    `users`.`id`,
    `email`,
    `last_name`,
    `first_name`,
    `active`,
    `users`.`created_at`,
    `last_login`
from
    `users`
where
    `users`.`deleted_at` is null

Which is completely different from the first query that is working fine.

If I use $userModel the result of course is wrong, it returns all the users available on the table, but the result must be the same of auth()->user()->patients.

What I did wrong?

CodePudding user response：

1. The main select() must include id from users.
2. The subquery select() must include user_id from patients

$userModel::select('id', 'and_other_user_columns')
    ->with(['patients' => function ($query) {
    $query->select('user_id', 'and_other_patient_columns');
}])
    ->get();

CodePudding user response：

You can use the with method. But you must include the patients id in your query.

return $userModel::with('patients:id, email, last_name, first_name, active, last_login')->select(['id','created_at'])->get();