How to get pivot data inside eloquent withCount function callback?

Question

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 a belongsToMany() relation with the Cluster() model
• The Cluster() model has a belongsToMany() relation with the User() model
• The Cluster() model has a hasMany() relation with the Messages() model
• The Message() model has a belongsTo() relation with the Cluster() 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!

Answer 1

Found the answer due to a comment of @cbaconnier.

Placing DB::raw('cluster_user.last_read_message_id') on the spot is working. I't not neat, but it works.

Full example:

$clusters = Auth::user()
     ->clusters()
     ->withCount(['messages' => function ($query) {
            $query->where('messages.id', '>', DB::raw('cluster_user.last_read_message_id'));
     }])
     ->get();