Laravel MySQL orderBy count

问题:

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();
});

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

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

And in Comment model:

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

回答1:

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).

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;
}

and now you could use:

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

to sort ascending or

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

to sort descending.

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

回答2:

I think I've come up with a workaround:

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

This one order by the number of comments ascendingly, if you want to order by it descendingly, do this:

$posts = Post::with('comments')->get()->sortBy(function($post) {
    return $post->comments->count();
})->reverse();