How to do a JOIN on table in a HasMany relationship, if only the latest row on the joined table is searched or?

ivymaster 0
Viewed 36

I have a Post and PostComment model. They are in a HasMany relationship, as the picture suggests. ER diagram

I need to return all posts, and, the latest comment for each post, paginated.

The problem is that I need also to do a search on the "post_comments" table. Something like "get all posts from a specific user, ...". So I decided to use the JOIN.

My problem is that with JOIN I do not receive only one entry per post, but rather as many post entries, as there are comments in the related table.

I tried to group the result by "post_id", but some critics said that this is not good. What would be the "proper approach " for this?

eloquent sql php laravel
1 Answers

The proper approach since you are using laravel and not pure PHP is to use eloquent for this, so the general idea is this

$posts = Post::whereHas('comments')->with('comments')->sortByDesc('comments.created_at')->paginate(10);

And then when you foreach these posts in your blade or vue, you just need to do this

foreach($posts as $post) {
   $post->comments()->latest()->first(); // this the latest comment
}
dz0nika0
Related