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

classPostextendsModel{publicfunctioncomments(){return$this->hasMany(Comment::class);}}classCommentextendsModel{}$posts=Post::withCount('comments')->get()->sortBy('comments_count');

Answer 2

试试SELECTpost_id,count(1)ascomment_countFROMcommentsGROUPBYpost_idORDERBYcomment_countLIMIT10000