I'm new to Laravel and MySQL, i couldn't figure out how to use a single join query to get user, their posts, and post comments. I'm not sure if i'm doing it right, but there must be a better way to fetch posts and post comments in a structured way. This is what I've done so far. I'm using $username to lookout for every single post user has made in Posts table and then looping through each post to see if that specific post has any comments with post.id.
It's working perfectly, but i feel that it's a bit expensive way to loop through each single posts to find it's comments with an individual query. Is there a better way to do it in a single query and keep data structured so that i can easily populate my view?
public function user($username){
$user = User::where('username',$username);
if($user->count()){
$user = $user->first();
$posts = Posts::where('username', $username)->orderBy('created_at', 'DESC')->orderBy('at','DESC')->paginate(4);
if(count($posts)>0){
$i=0;
foreach($posts as $data){
$ago = date('Y-m-d', strtotime($data->created_at)).' '.$data->at;
$comments = Comments::where('post_id',$posts[$i]->id)->get();
$posts[$i]->at = ProfileController::getElapsedTime($ago);
$posts[$i]->comments = $comments;
$j=0;
foreach($posts[$i]->comments as $comments){
$c_time = date('Y-m-d', strtotime($comments->created_at)).' '.$comments->at;
$posts[$i]->comments[$j]->at = ProfileController::getElapsedTime($c_time);
$comment_user = User::where('username', $posts[$i]->comments[$j]->comment_user)->first();
$posts[$i]->comments[$j]->user_dp = $comment_user->dp_uri;
$posts[$i]->comments[$j]->name = $comment_user->name;
$j++;
}
//echo $posts[$i]->comments.'<br><br>';
$i++;
}
}
return View::make('profile.posts')
->with('user',$user)->with('posts', $posts);
}else{
return Response::view('errors.missing', array(), 404);
}
}