Question Conversations Between 2 users

[commandantp]

Hi! Nice build, Quick one I have been trying to get all the conversation between 2 users, meaning I have both their IDs. Do you have that already built in? I am trying to make that but cannot figure out how... strangely enough. Thanks! EDIT :+1: HERE IS THE SOLUTION

    public function scopeForUserWithFriend($query, $user = null, $friendUser) {
        $user_id = $user->id;
        $friendUser_id = $friendUser->id;

        return $query->join('participants', function($join) use ($user_id, $friendUser_id) {
            $join->on('conversations.id', 'participants.conversation_id')
            >where('user_id', $friendUser_id);
        })
        ->with('participants')
        ->where('participants.user_id', $friendUser_id)
        ->where('participants.deleted_at', NULL)
        ->select('conversations.*'); 
    }   

[andheiberg]

What about it doesn't work? What does it result in? Have you tried incrementally adding where's to see what it is that isn't behaving as you are expecting?

It looks correct to me, but I don't have a db to run it on right now.

[commandantp]

Returns an empty array even when I have a conversation share by two participants object(Illuminate\Database\Eloquent\Collection)#236 (1) { ["items":protected]=> array(0) { } }

[commandantp]

By the way, is the forUser scope I add to modify the 'where(deleted_at)toparticipants.deleted_at` or I would get ambiguity constrain problem. See below the error for the query I wrote above if I keep just delete_at.

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'deleted_at' in where clause is ambiguous (SQL: select conversations.* from conversations inner join participants on conversations.id = participants.conversation_id where conversations.deleted_at is null and participants.user_id = 9 and participants.user_id = 7 and deleted_at is null)

[commandantp]

ended up working.....

[andheiberg]

Please post you solution for people with the same issue as you in the future :) thanks

[commandantp]

added in the top message :-) - maybe could even be improved