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();});democomments表有如下数据idpost_idbody11?到此一游22此文嘉奖31♥不错42♥不错+151♥不错+261♥不错+371♥不错+4最终希望查询出如下post_idcomment_count1522

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