Working out a trending query - Laravel Eloquent

Question

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

Do you have any colomns where you are storing the number of views for the article. if you have then you can also include it in the query, make use again of orderBy('number_views', 'DESC') — Oli Soproni B., Apr 14 '16 at 06:10
It appears he has one line for each view on one article, given the `one-to-many` relationship. Ideally, he should do a count on the views in one query and inner join the latter to the actual articles query. — Mysteryos, Apr 14 '16 at 06:27
Yes I have a article_views table which stores the article_id, ip address & created_at — Josh, Apr 15 '16 at 01:39
@Mysteryos could you please explain how this would be possible to achieve in a laravel model? — Josh, Apr 15 '16 at 03:19

Cptmaxon · Accepted Answer · 2016-04-14T10:58:11.627

you have several approaches you can take,

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.

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

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

score 2 · Answer 2 · answered Apr 14 '16 at 11:04

A performance-focused solution should be either:

A) Optimize Query Action, slightly slower View Action: Update a column every time there is a view and then query by ordering that column - best solution is adding a trigger to mysql everytime a view is added to update the viewed column in the article.

B) Optimize View Action, much slower query action: Don't do anything when views are added, add a temporary column that is a count of views and order by this temporary column. Fastest way would be with SQL something like

select article_name, (select count(*) from views where view.article_id = articles.article_id) as view_count from articles order by view_count

This can be translated to laravel using a raw select or using a filter on the collection like @Cptmaxon suggested which is slower.

Working out a trending query - Laravel Eloquent

2 Answers2