laravel eloquent nested SELECT statement

Question

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.

Using JOIN? w3schools.com/sql/sql_join.asp

holland
– holland

2017-08-29 12:16:33 +00:00
Commented Aug 29, 2017 at 12:16 — holland
– holland, Commented Aug 29, 2017 at 12:16
You have to use eloquent relationships..

Muhammad Rizwan
– Muhammad Rizwan

2017-08-29 12:36:57 +00:00
Commented Aug 29, 2017 at 12:36 — Muhammad Rizwan
– Muhammad Rizwan, Commented Aug 29, 2017 at 12:36

Maulik · Accepted Answer · 2017-08-29 12:31:39Z

1

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.

answered Aug 29, 2017 at 12:31

Maulik

3,0211 gold badge25 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeet Tupe Over a year ago

Well Explained! Thanks a lot Maulik. Worked Like charm

Maulik Over a year ago

welcome :) you should mark it as answered by clicking tick button under down arrow.

ishegg · Accepted Answer · 2017-08-29 12:34:01Z

0

This should work. Try the following:

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

edited Aug 29, 2017 at 12:34

ishegg

9,9973 gold badges20 silver badges31 bronze badges

answered Aug 29, 2017 at 12:22

bhill77

87511 silver badges12 bronze badges

1 Comment

Jeet Tupe Over a year ago

That, unfortunately, is returning NULL at key user as "user"=>NULL

Suniti Yadav · Accepted Answer · 2017-08-29 12:37:39Z

0

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.

answered Aug 29, 2017 at 12:37

Suniti Yadav

4033 silver badges8 bronze badges

Collectives™ on Stack Overflow

laravel eloquent nested SELECT statement

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related