Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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');
}
like image 942
dulan Avatar asked Oct 15 '14 06:10

dulan


2 Answers

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

like image 145
Marcin Nabiałek Avatar answered Oct 21 '22 23:10

Marcin Nabiałek


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();
like image 37
dulan Avatar answered Oct 21 '22 23:10

dulan