CODEWITHSUNDEEP
sortinglaraveleloquenttrending
Josh
Josh

Reputation: 3100

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

Upvotes: 2

Views: 1297

Answers (2)

NiRR
NiRR

Reputation: 5002

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.

Upvotes: 2

Cptmaxon
Cptmaxon

Reputation: 525

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

Upvotes: 5

Related Questions