CODEWITHSUNDEEP
laraveleloquentlaravel-5
Aakash
Aakash

Reputation: 226

Laravel 5.1 - Trending Posts - Query on relationship count with timestamp condition of pivot

I am using laravel 5.1 for a CMS development. I have a simple structure of posts, users and users can like posts.

I want to list the trending posts (posts with most likes in last 1 week in desc order). Posts and Users have many-to many relationship and use a pivot table for relationship.

Posts Model has

public function likedby()
{
    return $this->belongsToMany('App\Models\User','user_like_post')
        ->withTimestamps();
}

User Model has

public function likes(){
    return $this->belongsToMany('App\Models\Post','user_like_post')
        ->withTimestamps();
}

How can I write eloquent query so I receive the trending post. I need to use the timestamp of the pivot table which I find difficult to use.

This is what I tried but it used 'created_at' of post table and not of pivot table.

$trending = Post::with('likedby')->get()
    ->sortByDesc(function ($post){
        $one_week_ago = Carbon::now()->subWeeks(1); 
        return $post->likedby
            ->where('created_at','>=',$one_week_ago)
            ->count();
    }
);

Upvotes: 0

Views: 1093

Answers (1)

David Barker
David Barker

Reputation: 14620

You can constrain an eager load within the with method, however this would still load all the posts but only eager load the likedby relationship when it is less than one week old. This is probably not the behaviour you want, but worth mentioning.

Post::with(['likedby' => function($query) {

    $query->where('created_at', '>=', Carbon::now()->subWeeks(1));

}])->get();

To only load posts that have been liked within the last week, you would be best served by the whereHas method. This will provide you with a list of posts that have had likes placed on them within the last week.

Post::whereHas('likedby', function ($query) {

    $query->where('created_at', '>=', Carbon::now()->subWeeks(1));

})->get();

Upvotes: 1

Related Questions