duanqian6295
2019-02-01 12:45
浏览 83
已采纳

如何使用计数函数回调在eloquent中获取数据透视数据?

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!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

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()
         ->clusters()
         ->withCount(['messages' => function ($query) {
                $query->where('messages.id', '>', DB::raw('cluster_user.last_read_message_id'));
         }])
         ->get();
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • droi5225 2019-02-01 13:06

    Good question! I think you should be able to do use the withPivot method on your relationship and then use the pivot attribute in your query.

    For example, in your Cluster model where you define the cluster_user relationship, do:

    function cluster_user() {
        return $this->belongsToMany('App\User')
                    ->withPivot('last_read_message_id');
    }
    

    And then in your query you could use whereColumn to compare the columns. Something like this:

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

    Search for "Retrieving Intermediate Table Columns" on the Eloquent relationships documentation for more information.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题