How do i fetch the latest comment for with that specific model, and the user for that comment?

Question

I have a model that is "static". Its contains games with various information about them. And users can comment on games with a polymorphic relationship. The problem is that I want to fetch the 10 latest games that has been commented on, and that user for that comment. So Game doesn't have a relationship to Users.

I have manage to solve it like this:

$games = Game::join('comments', 'games.id', '=', 'comments.commentable_id')
->where('comments.commentable_type', Game::class)
->latest('comments.created_at')
->groupBy('games.id')
->take(10)
->withCount('comments')
->get()->each(function($game){
    $game->comment = $game->comments()->orderBy('created_at', 'desc')->first();
    $game->user = User::find($game->comment->user_id);
    return $game;
});

But this creates a lot of queries, I would like to eager load, don't have that N+1 problem everyone is talking about.

I got another solution to add this function to Game

class Game extends Model {
    public function latestComment() {
        return $this->morphOne(Comment::class, 'commentable')->latest();
    }
}

And run the query like $games = Game::withCount('comments')->with(['latestComment.user'])->take(10)->get();

But it seems as this query doesn't fetch the users. Only the first user is fetched. And also the comments_count (withCount()) only returns a value for the firsts result.

So I'm stuck!

The expected result Load the 10 latest games with the latest comment for that game, with the totalt count of comments for that game, with the user for the latest comment - everything eager loaded to avoid the n+1 problem.

Is it possible? ps. prepared a sqlfiddle http://www.sqlfiddle.com/#!9/c07570

Edit It seems as the second query in this question "takes" (take(10)) takes the first 10 games in from games table, regardless if they have comments or not. If I have 15 comments for games with IDs from 20-30, the query will check IDs from 1 to 10.

If I run the second query in my question, with just the fiddle data in my comment table. Result 10 will contain game with id of 10, no user, no comments_count etc.

Did you see https://stackoverflow.com/questions/36755804/laravel-eloquent-relations-latest ? — alariva, Aug 15 '18 at 21:04
I don't think so, but I have updated the fiddle and removed unnecessary data. — Adam, Aug 16 '18 at 04:40
Your `games.id` values are `1...9`, your `comments.commentable_id` values are `1, 14, 139, 242`. So, only the first game actually has comments. — Jonas Staudenmeir, Aug 16 '18 at 13:32
@JonasStaudenmeir I updated the fiddle. Say that it is only one comment in `comments` table. The query seems to fetch 10 games either way. — Adam, Aug 16 '18 at 14:49
If you want to ignore games without comments, you can add `->having('comments_count', '>', 0)` to the query (doesn't work with pagination). — Jonas Staudenmeir, Aug 16 '18 at 15:31
@JonasStaudenmeir That solves the problem with fetching uncommented games, yes - but; the query still only take games in IDs order from the games table. It doesn't fetches the 10 games, with the latest comments. — Adam, Aug 16 '18 at 15:55
You want to order the games by the latest comment's `created_at`? — Jonas Staudenmeir, Aug 16 '18 at 20:27

score 0 · Answer 1 · answered Aug 18 '18 at 02:13

0

You can use a modified withCount() to get the latest comments.created_at:

$games = Game::withCount(['comments',
        'comments as latest_comment' => function($query) {
            $query->select(DB::raw('max(created_at)'));
    }])
    ->having('comments_count', '>', 0)
    ->orderByDesc('latest_comment')
    ->with('latestComment.user')
    ->take(10)
    ->get();

answered Aug 18 '18 at 02:13

Jonas Staudenmeir

24,815
6
63
109

Can you please give feedback on the answer? – Jonas Staudenmeir Aug 25 '18 at 22:51

Erubiel · Answer 2 · 2018-08-18T07:21:18.050

0

I would access directly the comments table/model, query for the last 10 comments, pluck the games ids, and use them to query the games model/table with a whereIn.

Also add to the games query a selectRaw('(SELECT COUNT(*) FROM comments where ...) as commentCount')

edited Aug 18 '18 at 07:21

answered Aug 18 '18 at 05:39

Erubiel

2,934
14
32

How do i fetch the latest comment for with that specific model, and the user for that comment?

2 Answers2