CODEWITHSUNDEEP
phplaravellaravel-5laravel-5.3
Jeet Tupe
Jeet Tupe

Reputation: 25

laravel eloquent nested SELECT statement

I am using Laravel with Eloquent with MySQL.
I have three tables POSTS, USERS and COMMENTS. My POST and COMMENT are in one-to-many relation thus with the following code I get the list of Comments for particular POST id 

$post =  Post::where('id', $id)->get();
$comment = Post::find($id)->comment()->paginate(10);       
return view('posts\single', ['post' => $post, 'comments' => $comment]);

Now I want to get the Details of each user from the USER table. I don't want to use the foreach() and then get details of every single ID. Is there any method by which I can get all the Comments (from COMMENTS table) including user details (from USER table) at a single call.

Upvotes: 2

Views: 627

Answers (3)

Suniti Yadav
Suniti Yadav

Reputation: 403

Please try with this -

User::join('posts','posts.created_by','=','users.id')
      ->with('comment')
      ->where('posts.id' ,'=' ,$id)
      ->paginate(10);

Hope this will helpful for you.

Upvotes: 0

bhill77
bhill77

Reputation: 875

This should work. Try the following:

$comment = Comment::where('post_id', $post->id)->with('user')->paginate(10);

Upvotes: 0

Maulik
Maulik

Reputation: 2991

you first need to create one to many relationship in USERS to COMMENTS (user have many comments).

and then you already have relation post have many comments relationship.

so, let's use Nested Eager Loading

$post =  Post::where('id', $id)->with('comments.user')->get();
return view('posts\single', ['post' => $post]);

now in your blade files....

  • $post->comments will give you all comments for particular post.
  • $post->comments->user will give you user who made a comment.

Upvotes: 1

Related Questions