1

Version

Laravel : 7.28.3

mysql : Ver 14.14 Distrib 5.7.29, for osx10.15 (x86_64) using EditLine wrapper

Tables

  1. contents (id)
  2. content_views (id, content_id)

What I'm Trying To Do

I would like to get a rank of a content by how many it has content_views.

Code

App/Models/Content.php

  /**
   * Attribute of get rank by views
   *
   * @return Int
   */
  public function getViewsRankingAttribute()
  {
      DB::statement(DB::raw('set @c=0'));
      $result = collect(
          DB::select('select rank from
          (
              select _ranking.*, @c:=@c+1 as rank from
              (
                  select content_views.content_id, count(content_views.id) as views
                  from content_views
                  group by content_views.content_id
                  order by views desc
              ) as _ranking
          ) as ranking
          where content_id = :contentId', [
              'contentId' => $this->id
          ]))
          ->first();
      return $result ? $result->rank : '-';
  }

This is actually working in mysql 5.8 but it's not in 8.0.

Error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from ( select _ranking., @c:=@c+1 as rank from ' at line 1 (SQL: select rank from ( select _ranking., @c:=@c+1 as rank from ( select content_views.content_id, count(content_views.id) as views from content_views group by content_views.content_id order by views desc ) as _ranking ) as ranking where content_id = :contentId)

GMB
  • 216,147
  • 25
  • 84
  • 135
user14212874
  • 65
  • 7
  • The arrival of CTE in 8 has dispensed with the need for these kinds of variables. But seeing as you're parsing the result in PHP, why not handle the ranking there? – Strawberry Oct 03 '20 at 07:49
  • @Strawberry: OP wants the rank of a particular content only. To do this in PHP, they would need to fetch the entire dataset (grouped by content), then iterate in the application - which looks suboptimal. – GMB Oct 03 '20 at 15:47

2 Answers2

0

MySQL 8.0 introduced window functions, which make it much easier to perform ranking tasks. On the other hand, user variables are officially planned for deprecation in future versions.

Time to embrace the future! You can rewrite your query as follows:

select *
from (
    select content_id, count(*) as views, rank() over(order by count(*) desc) rn
    from content_views
    group by content_id
) t
where content_id = :content_id
GMB
  • 216,147
  • 25
  • 84
  • 135
0

This actually worked!

public function getViewsRankingAttribute()
{
    $rank = '-';
    if ($this->views->isNotEmpty()) {
        $views = $this->views->count();
        $rank = Content::withCount('views')
            ->having('views_count', '>', $views)
            ->get()
            ->count();
        $rank++;
    }
    return $rank;
}
user14212874
  • 65
  • 7