Laravel Query Builder - problem with two Join

Question

Currently I have a query that takes the posts and searches through Join some user data

$posts = \App\Post::join('users', 'users.id', '=', 'posts.author_id')
  ->select('posts.id', 'posts.title', 'posts.excerpt', 'posts.slug', 'posts.created_at', 'posts.image', 'users.avatar', 'users.name', 'users.username')
  ->orderBy('posts.created_at', 'desc')
  ->paginate(14);

I recently implemented a simple like system, facebook style and would like to add in the same query the sum of Likes according to the post

I tried to add like in the same query as follows

$posts = \App\Post::join('users', 'users.id', '=', 'posts.author_id')
  ->join('likes', 'posts.id', '=', 'likes.post_id')
  ->select('posts.id', 'posts.title', 'posts.excerpt', 'posts.slug', 'posts.created_at', 'posts.image', 'users.avatar', 'users.name', 'users.username', DB::raw('count(likes.post_id) as likeCount'))
  ->groupBy('likes.id')
  ->orderBy('posts.created_at', 'desc')
  ->paginate(14);

How could I resolve the query ? Join solves or better another method.

Answer 1

Join solves, but you can simplify creating relationships between models.

Example: In the Post template you add the following (This is examples):

public function user()
{
      return $this->belongsTo('App\User', 'user_id');
}



public function likes()
{
      return $this->hasMany('App\Likes', 'post_id');
}

Then in the query just do:

\App\Post::with('user','likes')->orderBy('created_at', 'desc')->paginate(14);

You can read a little more about it here: https://laravel.com/docs/5.7/eloquent-relationships