2019-02-01 12:45
浏览 83


I'm trying to use a pivot variable of a parent relationship inside the eloquent withCount() method.


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', '>', '???');

I've already tried:

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

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');

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)


  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • dongxingdu9594 2019-02-01 13:12

    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()
         ->withCount(['messages' => function ($query) {
                $query->where('messages.id', '>', DB::raw('cluster_user.last_read_message_id'));
    解决 无用
    打赏 举报