My question is regarding ensuring a unique array of users in a related model using Eloquent's query builder.
One feature of an app I am working on displays a list of active conversations with other users, text messenger style. Each conversation should preview the most recent message received. Since conversations can be carried out between multiple users, a conversation between you and John should be different from a conversation between you, John, and Jane.
I've set up two relevant models: Message and User. Message is related to User in the following way:
public function sent_to() {
return $this->belongsToMany('App\User', 'message_users');
}
I am trying to return a list of unique conversations in my message controller like so:
$show = \App\Message::where('team_id', $team_id)
->where('user_id', Auth::user()->id)
->with(['sent_to' => function($query) {
$query->distinct();
}])
->with('user')
->orderBy('created_at', 'desc')->skip($offset)->take($limit)->get();
return $show;
The ->with(['sent_to'... section is where I'm a bit lost. Of course, the intent is that I get a list of unique addressees; however, I get all results. Any help is appreciated!
Update using jedrzej.kurylo's suggestion
jedrzej.kurylo suggested the following:
$show = \App\Message::where('team_id', $team_id)
->where('user_id', Auth::user()->id)
->with(['sent_to', function($query){
$query->groupBy('id');
}])
->with('user')
->orderBy('created_at', 'desc')->skip($offset)->take($limit)
->get();
return $show;
This yields the following error:
Since this is a many-to-many relationship (User linked to Message via 'message_user' table), 'id' actually refers to the id of the pivot table. What I would actually like is to get the pivot table's 'user_id'. Changing to ->groupBy('user_id') (a value on the pivot table) yields the following error: "mb_strpos() expects parameter 1 to be string, object given." Which is a radically different error.
I'm working on a work-around, and will update with it when I get it working--but it will require a couple more explicit queries. I feel like this is should be possible!
