Better query with relations then this

Hi, I have db table with tournaments. Every tournaments has meny games. Every game has meny "plays" and every play belongs to user.

My current query looks like this

$tournament = $this->getTournament($slug);
$gamesRounds = $tournament->games->groupBy('round');

which gives me collections of rounds which contains collections of games... and this is what I want.

in the view I display it in the following way

@foreach($gamesRounds as $key => $round)
    @foreach($round as $game)
        @for($i = 0; $i<count($game->plays); $i++)
            {{ $game->plays[$i]->user->first_name }}
        @endfor
    @endforeach 
@endforeach

My problem is about displaying first_name because in debuger I see multiple and dubplicatend queries like this select * from users where users.id = '3' limit 1 ( in 2 rounds and 6 games -> more then 40 queries )

Is it a way to improve this script/query ?

lostdreamer_nl

7 years ago

Best Answer

Level 53

in your getTournament method, you are doing a query like:

$tournaments = Tournament::with('games')->get();

Assuming a game has a user_id linking it to a user, you can change that query to look like this

$tournaments = Tournament::with('games.user')->get();

Now it will eager load all those users as well in another single query

If that doesnt work, please post the contents of:

the getTournament method
your Tournament / Game & User models

With that info we should be able to point you exactly to where to eager load the data.

rouge

7 years ago

Level 11

It's a classic n+1 problem. You can fix it by eager load the plays and user first before iterating the $game in a for loop.

You can watch the lesson linked below to see the workflow to fix this problem.

https://laracasts.com/series/lets-build-a-forum-with-laravel/episodes/20

GreenGo

7 years ago

Level 6

@lelostdreamer_nl

big THX with('games.plays.user') solved my problem and reduced number of queries :)

Please or to participate in this conversation.