CODEWITHSUNDEEP
phplaraveleloquentmany-to-many
Ben
Ben

Reputation: 11188

Laravel Eloquent many to many filter on intermediate table

I'm using a User/Post example in the question. I have two tables Users and Posts linked together with a many to many relationship with post_views as table. Also I've created an Event and Listener to handle the views on a post. But I only want to add a new view-entry if the last view for a given post was over an hour ago by that user.

At the moment my handle() method in the UpdatePostViews listener does:

public function handle(PostWasViewed $event)
{
    $event->post->views()->attach($event->user->id);
}

The relationship is defined with withTimestamps But how can I filter in the handle() method to do something like 

    $lastView = User::where('user_id', $userId)
->where('post_id', $postId)
->in('post_views')
->orderBy('id', 'DESC')
->first();

which would return the last row for a user/post combination from the post_views so I can determine on the timestamps it was inserted over an hour ago and add a new entry or skip creation of a new entry.

Upvotes: 1

Views: 1036

Answers (1)

patricus
patricus

Reputation: 62238

You can use the wherePivot method on the BelongsToMany relationship to add constraints on the pivot table for the query.

Once you do that, you can use any combination of logic to determine if there was a view in the last hour. Something like:

public function handle(PostWasViewed $event)
{
    $inLastHour = $event->user->posts()->where('id', $event->post->id)->wherePivot('updated_at', '>=', new \Illuminate\Database\Query\Expression('NOW() - INTERVAL 1 HOUR'))->exists();

    // or, either way, shouldn't matter

    $inLastHour = $event->post->views()->where('id', $event->user->id)->wherePivot('updated_at', '>=', new \Illuminate\Database\Query\Expression('NOW() - INTERVAL 1 HOUR'))->exists();

    if (!$inLastHour) {
        $event->post->views()->attach($event->user->id);
    }
}

Upvotes: 4

Related Questions