laravel 根据留言表 查询出留言最多的文章，热度排行榜查询

Question

mysql 小白

希望查询出文章留言的 排行榜

希望找个大神 指导一下 。

comments 评论表

Schema::create('comments', function (Blueprint $table) {
      $table->increments('id');   
      $table->string('post_id')->notNull()->comment('文章id');
      $table->string('body')->notNull()->comment('留言内容');
      $table->timestamps();
});

posts 文章表

Schema::create('posts ', function (Blueprint $table) {
      $table->increments('id');
      $table->string('title')->notNull()->comment('文章标题');
      $table->string('body')->notNull()->comment('文章内容');
      $table->timestamps();
});

demo

comments 表有如下数据

id	post_id	body
1	1	?到此一游
2	2	此文嘉奖
3	1	♥不错
4	2	♥不错+1
5	1	♥不错+2
6	1	♥不错+3
7	1	♥不错+4

最终希望查询出如下

post_id	comment_count
1	5
2	2

Answer 1

class Post extends Model
{
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

class Comment extends Model
{

}

$posts = Post::withCount('comments')->get()->sortBy('comments_count');

Answer 2

试试 SELECT post_id,count(1) as comment_count FROM comments GROUP BY post_id ORDER BY comment_count LIMIT 10000