Laravel MySQL的orderBy计数

问题描述

我使用Laravel和MySQL，我有一个表 post ，表示用户可以评论的帖子，现在我想通过每个帖子的评论数量升序/降序，我该如何在Laravel？我不想在 post 表中添加一个字段，以跟踪每篇文章的评论数量，因为每次添加/删除评论或评论的评论时手动更新该字段会驱使我crazy ...

I'm using Laravel and MySQL, and I have a table post that represents post where users can comment on it, now I wanna order posts by the number of comments of each post in ascending/descending order, how do I do this in Laravel? I don't want to add a field in post table to keep track of the number of comments of each post, because updating that field manually each time a comment or a comment's comment is added/deleted drives me crazy...

这是我创建我的帖子表和注释表：

This is how I create my posts table and comments table:

Schema::create('posts', function($table) {
    $table->increments('id');
    $table->string('title', 100)->unique();
    $table->string('content', 2000);
    $table->timestamps();
});
Schema::create('comments', function($table) {
    $table->increments('id');
    $table->string('content', 2000);
    $table->unsignedInteger('post_id');
    $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade')->onUpdate('cascade');
    $table->unsignedInteger('parent_id')->nullable();
    $table->foreign('parent_id')->references('id')->on('comments')->onDelete('cascade')->onUpdate('cascade');
    $table->timestamps();
});

这是我如何在我的Post模型中设置帖子和评论之间的关系：

And this is how I setup relationship between posts and comments in my Post model:

public function comments() {
    return $this->hasMany('Comment', 'post_id');
}

在评论模式中：

public function post() {
    return $this->belongsTo('Post', 'post_id');
}

推荐答案

显示，但现在你从数据库中获取所有条目。如果你将有100个帖子，每个有100条评论，你将从数据库中获取10000行，只是为了排序你的帖子（我假设你不想在排序时显示这些评论）。

You can do that as you showed but now you get all entries from database. If you will have 100 posts each with 100 comments, you will get 10000 rows from your database just to sort your posts (I assume you don't want to display those comments when sorting).

您可以添加到您的Post模型：

You could add to your Post model:

public function commentsCountRelation()
{
    return $this->hasOne('Comment')->selectRaw('post_id, count(*) as count')
        ->groupBy('post_id');
}

public function getCommentsCountAttribute()
{

    return $this->commentsCountRelation ?
        $this->commentsCountRelation->count : 0;
}

现在您可以使用：

$posts = Post::with('commentsCount')->get()->sortBy(function($post) {
    return $post->comments_count;
});

可按升序排序或

$posts = Post::with('commentsCount')->get()->sortBy(function($post) {
    return $post->comments_count;
}, SORT_REGULAR, true);

按降序排序。

方法使用 sortBy 和更高版本反向不是一个好主意应该使用参数sortBy如我所示

By the way using sortBy and later reverse is not a good idea you should use parameters to sortBy as I showed

这篇关于Laravel MySQL的orderBy计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！