CODEWITHSUNDEEP
sqllaraveleloquent
Michel Gisenaël
Michel Gisenaël

Reputation: 351

Laravel whereHas in many to many column id ambigious

There are my 3 tables:

- conversations:
  - id
  - name
- users:
  - id
  - name
- conversation_user:
  - id
  - conversation_id
  - user_id

Model conversation:

public function members() {
   return $this->belongsToMany(User::class, 'conversation_user');
}

Model user:

public function conversations() {
    return $this->hasMany(Conversation::class, 'conversation_user');
}

I want to retrieve if 2 users has already a conversation set.

My request

Conversation::withCount('members')->having('members_count', '==', 2)
->whereHas('members', function($query) use($user){
       $query->where('id', $user);
})->whereHas('members', function($query) use($auth){
       $query->where('id', $auth->id);
})->first();

1 conversation has at least 2 members, so it can have more; but here in my functionality I have input that search for users, and on click a user, I want to retrieve if the user logged in and the user selected has already a conversation.

But with my query I get this error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select conversations., (select count() from users inner join conversation_user on users.id = conversation_user.user_id where conversations.id = conversation_user.conversation_id) as members_count from conversations where exists (select * from users inner join conversation_user on users.id = conversation_user.user_id where conversations.id = conversation_user.conversation_id and id = 2) and exists (select * from users inner join conversation_user on users.id = conversation_user.user_id where conversations.id = conversation_user.conversation_id and id = 1) having members_count = == limit 1)

Upvotes: 1

Views: 950

Answers (1)

OMR
OMR

Reputation: 12188

you have to set the table names to avoid ambiguity:

Conversation::withCount('members')->having('members_count', '==', 2)
->whereHas('members', function($query) use($user){
       $query->where('users.id', $user);
})->whereHas('members', function($query) use($auth){
       $query->where('users.id', $auth->id);
})->first();

Upvotes: 1

Related Questions