majidarif majidarif - 4 months ago 53
MySQL Question

Laravel hasMany relation count number of likes and comments on post

The code:

$posts = Jumpsite::find($jid)
->posts()
->with('comments')
->with('likes')
->with('number_of_comments')
->with('number_of_likes')
->where('reply_to', 0)
->orderBy('pid', 'DESC')
->paginate(10);


Each post has a comment and likes. I only display a few of the comments initially to avoid large loads. But I want to show how many the total comments and likes for each post. How do I do this?

Model code:

public function likes()
{
return $this->hasMany('Like', 'pid', 'pid');
}

public function comments()
{
return $this->hasMany('Post', 'reply_to', 'pid')->with('likes')->take(4);
}

public function number_of_likes()
{
return $this->hasMany('Like', 'pid', 'pid')->count();
}


Note:

This is an API. All will be returned as JSON.





update

The return

Post
author_id
message
Comments(recent 4)
user_id
message
post_date
Number_of_likes
Likes
user_id
Number_of_total_comments
Number_of_total_likes





update

How I return the data

$posts = $posts->toArray();
$posts = $posts['data'];

return Response::json(array(
'data' => $posts
));


Just by using that I get all the data i want in the json. But I also want to add the total counts.




update

protected $appends = array('total_likes');

public function getTotalLikesAttribute()
{
return $this->hasMany('Like')->whereUserId($this->uid)->wherePostId($this->pid)->count();

}


but getting the error:

Unknown column 'likes.post_id'





error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'likes.post_id' in 'where clause' (SQL: select count(*) as aggregate from `likes` where `likes`.`deleted_at` is null and `likes`.`post_id` = 4 and `pid` = 4 and `uid` = 1)

Answer

In your model place the following accessors:

Count total Likes:

 public function getTotalLikesAttribute()
 {
    return $this->hasMany('Like')->whereUserId($this->author_id)->count();

 }

Count total comments:

From your description, i can see, you have retrieving the number of posts as comments

public function getTotalCommentsAttribute()
{
    return $this->hasMany('Post')->whereUserId($this->author_id)->count();    
}

Now, from your controller:

$post  = Jumpsite::find($jid);

// total comments
var_dump( $post->total_comments );

// total Likes
var_dump( $post->total_likes );
Comments