Question 249960 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Trying to calculate the number of items in a table that have a relationship with another

I’m getting data from the posts table and there is a likes table that is related to this. I want to know how many likes each post has. I followed an example, but this way it gives an error.
I will leave the error after the code

$data = Post::query();
$data = $data->leftJoin('likes', 'posts.id', '=', 'likes.post_id');
$data = $data->selectRaw('posts.id, posts.status, COUNT(likes.id) AS curtidas')->groupBy('posts.id');
$data = $data->get();


Syntax error or access violation: 1055
Tags:

2

Answers


  1. 0

    You could also use Laravel’s Eloquent to achieve this; assuming there is a likes relationship between the Post and Like model, you could do this:

    $posts = Post::query()
    ->withCount('likes')
    ->get();

    Note that Laravel loads the number of likes in likes_count property on each post.

    Login or Signup to reply.
  2. 0

    To fix this issue, you can modify your query like this:

    $data = Post::query();
$data = $data->leftJoin('likes', 'posts.id', '=', 'likes.post_id');
$data = $data->selectRaw('posts.id, posts.status, COUNT(likes.id) AS curtidas');
$data = $data->groupBy('posts.id', 'posts.status'); 
$data = $data->get();

    But you can achieve the same result with a simpler query by using Laravel’s Eloquent relationships and the withCount method.

    //inside you Post modle
public function likes()
{
    return $this->hasMany(Like::class);
}

    Then, you can use the withCount method to retrieve the total likes for each post like this:

    $data = Post::withCount('likes')->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search