Count likes and comments

So is your concern here that you're doing a separate query for each count? You should be able to accomplish this in a single query easily by manually joining the tables.

Use the query builder. Here's some info from the docs on joins.

Thanks for the reply. I am currently using the approach you suggested.

My concern with this is when the 'likes' and 'comments' table grows really large (as I expect) then it could hit the performance when a user views the article as it would have to go through 2 large tables and do a count of the related likes and comments.

So, my question was more around using a solution which would keep a counter on the article record itself as well as keeping the data in the like and comment table. This would mean that whenever I load an article I would simply select the field with the count rather than joining and counting on a potentially massive table and counting live.

I just needed to know if there were any better options than the approach I have suggested.

Thanks again

With really strong indexes, it shouldn't be too much of a performance hit to count the number of comments or likes that are assigned to a particular article_id. Subqueries are useful things.

Having said that, if your queries end up too complicated (I've run into this problem counting messages in a conversation, for instance), I don't think there's anything wrong with keeping a counter if you just need to say 'Read x comments'.

The downside to this approach is that you have to keep those entity summaries within your article table. If you add support for pingbacks, for instance, and need to display those, then that's another field in your article table. Whether that's fine or not is up to you, but it's a valid thing to think about, especially after you have ten million articles in the table and now need to change the schema.

Benchmark! :)

I wouldn't worry about optimization too much at this point -- you can solve those problems down the road!

@Mike I had the exact same problem about a year ago and went with your solution (two extra columns in the posts table). I like to think of these as a form of "caching".

Don't think there's one correct answer here to be honest. I prefer this approach because it makes my classes so much cleaner.

Hi Mike I am in similar situation, can u help to get a single query to get the count of likes and comments joining both likes and comments table

You could have another table that gets updated with counts.

Are you using eloquent and eager loading? I have found by using a manually drawn query and use select raw I can easily query and count 1million+ rows in milliseconds

Eloquent I find and all the relationship stuff is dead slow

@Mathi Not quite sure what your situation is, but i can give an approach that i use myself.

Lets say you have the following Models:

• Article (id, title, body)
• Like (id, article_id)

<?php namespace App;
    
class Article{
    public function likes(){
        return $this->hasMany(App\Like::class, 'article_id', 'id');
    }

    # Use this to count the likes
    public function getLikeCountAttribute(){
        return $this->likes->count();
    }
}

# Sample usage
$article = App\Article::first();
$article->like_count;

OR Different situation, let's say you can give X amount of stars per rating, and you want to get the total count of all the stars:

Lets say you have the following Models:

• Article (id, title, body)
• Rating(id, article_id, stars)

<?php namespace App;
    
class Article{
    public function ratings(){
        return $this->hasMany(App\Rating::class, 'article_id', 'id');
    }

    # Use this to count the likes
    public function getStarCountAttribute(){
        return $this->ratings->sum('stars');
    }
}

# Sample usage
$article = App\Article::first();
$article->star_count;

you can used angular js

$data = Posts::join('likes', function($join){
        $join->on('likes.posts_id', '=', 'posts.id')->where('likes.status', '=', 
                     '1' )->where(post.status','active');
            })
                    ->groupBy('likes.posts_id')
                    ->orderBy( \DB::raw('COUNT(likes.posts_id)'), 'desc' )
                    ->select('posts.*')
                    ->paginate( '5' );