Level 42
I think you should change your Subject model like
public function User()
{
return $this->belongsToMany(User::class);
}
1 like
Nov 12, 2017
8
Level 2
How to whereHas when use belongsToMany relation?
I got a problem, When try to use whereHas in this case
Table users.
----------------
| id | name |
----------------
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
----------------
Table subjects.
------------------
| id | title |
------------------
| 1 | Subject 1 |
| 2 | Subject 2 |
------------------
Table subject_user.
------------------------
| user_id | subject_id |
------------------------
| 1 | 1 |
| 2 | 1 |
| 1 | 2 |
| 3 | 2 |
------------------------
in Subject Model
...
public function User()
{
return $this->belongsToMany(User::class, 'subject_user');
}
...
When I want to find subject by user_id with this query.
In this case Auth::id() == 1 and $request->user_id == 3
$subject = Subject::whereHas('User', function ($query) use ($request) {
$query->whereIn('user_id', [Auth::id(), $request->user_id]);
})->get();
With this query, I got subjects 1 and 2. That was a wrong result. That must got only subject 2.
Then I try this.
$subject = Subject::whereHas('User', function ($query) use ($request) {
$query->where('user_id', Auth::id())->where('user_id', $request->user_id);
})->get();
It would not get any subjects.
What query do I use in this case to get only subject 2.
Level 2
@saurabhd I think, That's not a problem.
Level 17
$subject = Subject::whereHas('User', function ($query) use ($request) { $query->whereIn('user_id', [Auth::id(), $request->user_id]); })->get();
Look at your code, you said, you have Auth::id() == 1 and $request->user_id == 3 and then you get subjects 1, 2 which is correct result according to your query.
If you want to search for only the subjects which belongs to the user_id that comes with the requestthen your both queries are wrong this could be as simple as:
If you want subjects specific to the user provided in the request:
$subject = Subject::whereHas('User', function ($query) use ($request) {
$query->where('user_id', $request->user_id);
})->get();
If you want subjects specific to the Logged in user :
$subject = Subject::whereHas('User', function ($query) use ($request) {
$query->where('user_id', Auth::id());
})->get();
or a different way could be:
$subjects = Auth::user()->subjects;
Level 2
@rumm.an That too close, But I'm not want all subjects of my user.
I want subject between my user and specific user.
Level 17
what do you mean when you say between your user and specific user?
Level 17
Do you mean, you want to retrieve subjects which are common for both users?
Level 2
@rumm.an Yes.
$subjects = Auth::user()->Subject()->whereHas('User', function ($query) use ($request) {
$query->where('user_id', $request->id);
})->get();
Level 1
You can do this with an inner join
$subjects = Subject::query()->whereHas('User', function ($query) use ($request) {
$query->where('user_id', $id1)->join(DB::raw('subject_user as su'),
'subject_user.subject_id', '=', 'su.subject_id')->where('su.user_id', $id2);
})->get();
Please or to participate in this conversation.