I'm trying to use a pivot variable of a parent relationship inside the eloquent withCount()
method.
Background:
There is a application with a ManyToMany relationship between Users and Clusters. Users can send messages within a cluster. To keep track of the unread message count for a user in a specific cluster i keep track of the last read message id in the join table, like so:
table: cluster_user
cluster_id | user_id | last_read_message_id
-------------------------------------------
1 | 59 | 3
2 | 62 | 8
- The
User()
model has abelongsToMany()
relation with theCluster()
model - The
Cluster()
model has abelongsToMany()
relation with theUser()
model - The
Cluster()
model has ahasMany()
relation with theMessages()
model - The
Message()
model has abelongsTo()
relation with theCluster()
model
Now I would like to list all the clusters of the authenticated user including a unread message count.
Currently I'm stuck on this:
$clusters = Auth::user()->clusters()->withCount(['messages' => function ($query) {
$query->where('messages.id', '>', '???');
}])->get();
I've already tried:
$clusters = Auth::user()->clusters()->withCount(['messages' => function ($query) {
$query->where('messages.id', '>', 'cluster_user.last_read_message_id');
}])->get();
But this gives me a total count of all the messages in stead of the ones with an id higher than x. If I hardcode an id, like this:
$clusters = Auth::user()->clusters()->withCount(['messages' => function ($query) {
$query->where('messages.id', '>', '3');
}])->get();
Then I get the correct unread message count.
So can somebody tell me how to use the pivot variable 'last_read_message_id' of the user()->cluster()
relationship inside the withCount()
callback function with the following in mind:
- I'ts crucial to use as little queries as possible.
- The unread message count must be a part of the cluster() collection because I'm returning a
ClusterResource
later on, like so:
return ClusterResource::collection($clusters);
which includes the unread message count.
class ClusterResource extends JsonResource
{
/**
* Transform the resource into an array.
*
* @param \Illuminate\Http\Request $request
* @return array
*/
public function toArray($request)
{
return [
'name' => $this->name,
'unread_messages_count' => $this->whenPivotLoaded('cluster_user', $this->messages_count)
];
}
}
Thnx!