CODEWITHSUNDEEP
phplaraveleloquent
Jouke
Jouke

Reputation: 362

How to get pivot data inside eloquent withCount function callback?

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

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:

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!

Upvotes: 4

Views: 3116

Answers (2)

D Malan
D Malan

Reputation: 11424

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.

Upvotes: 0

Jouke
Jouke

Reputation: 362

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

Upvotes: 2

Related Questions