Search code examples
sortinglaraveleloquenttrending

Working out a trending query - Laravel Eloquent

Hey guys I'm trying to develop a query which returns the trending articles from the database.

The trending articles are based on the most views over the last 24 hours. Here is the code so far:

$trending = Article::whereHas('view', function ($query) {
   $query->where('created_at', '>=', Carbon::now()->subHours(24));
})
->with('view')
->orderBy('created_at', 'DESC')
->get();

return $trending;
}

The article model has the following relationship:

public function view()
{
    return $this->hasMany('ArticleView', 'article_id');
}

The query works but I somehow need to also sort the articles by the view count. For example, the currently trending articles are displayed, but the artticles with the most view count are not ordered from first to last (obviously - they are ordered by created_at)

Help appreciated

Solution

  • you have several approaches you can take,

    1. like @Oli said, add a column to your table where you save the number_views of the last 24hrs, a trigger in the DB will keep it up to date. like every time there is a view it will recalc the field.

    2. add an appended 24h_views_count run your query and then sort in code 

      protected $appends= ['24h_views_count']

public get24hViewsCountAttribute(){
return $this->view()->where('created_at', '>=', Carbon::now()->subHours(24))->count();
}

//and after you get the result from trending just sort the collection via that property.
$trending->sortByDesc('24h_views_count');//this will sort it from highest to lowest

    3. the third option is to use SQL and it will look something like it looks here: https://laracasts.com/discuss/channels/general-discussion/eloquent-order-by-related-table