How to use pagination with where condition and with data from 3 tables?

For example I have three tables:

users(user_id),
game(game_id, name),
game_progresses(id, user_id, game_id, percent). // edited

I would like to return all the above data to a view and then display it in a table with columns: "Game name:" , "My progress:" (the percent on completion of the logged in player)

In my controller the paginated() function should return all the above data. Something like: return Game->with('user', 'game_progress')....

It would be awesome if I could also add filter like Show only games that have progress or Show games that don't have progress (by the logged in player) etc.

I'm especially struggling with the game_progress model. How do I define the connection/relation between the game AND only the logged in user. How would I achieve this?

The view would be something like this:

<thead>
    <tr>
        <th>Game name</th>
        <th>My progress</th>
    </tr>
</thead>
@foreach ($games as $game)
    <td>{{$game->name}}</td>
    <td>
    @if($game->game_progress)
        {{$game->game_progress->percent}}%
    @else
        -
    @endif 
    </td>
@endforeach

Thank you!

Snapey

9 years ago

Level 122

ignore pagination until you have a massive table laid out how you want it. You can then add pagination to the table in place of get()

I'm puzzled you don't have game_id in game_progress?

assuming that User has many game_progress and game_progress belongs to game

to get the user' progress

$games =Auth::user()->Game_progress::with('game')->get();

1 like

onujaar

9 years ago

Level 4

I'm so sorry, that was a typo, of course game_progress needs the game_id column. That is a good suggestion to get games which the player already has progress, but how would I also include the games which don't have progress yet or only get games which don't have progress yet?

In summary:

Games with progress => $games =Auth::user()->Game_progress::with('game')->get()
All games (both with progress and without) => ?
Only games without user progress => ?

Snapey

9 years ago

Level 122

All games;

// controller

$allgames = Game::orderBy('name')->get();

$progress = Auth::user()->Game_progress::with('game')->get()

return view('listing')->with(compact('progress', 'allgames');


// view

@foreach($allgames as $game)

    <tr>
    <td>{{ $game->name }}</td>

    <td>
        @if($progress->game->contains($game->id)
            {{ $progress->game[$game->id]->percent}}%  
        @else
            0%
        @endif
    </td>
    </tr>
@endforeach

this might work, but I can't try it. Basically, you list all the games and check if the user's collection of games in progress contains the current game. If so, use the current game as an index into the user's games in progress to select the right one to get the percentage from.

The other variants are the same, but skipping rows if the user did or did not have any progress.

In theory, you could filter the list of $allgames (bonus points) rather than skipping unwanted rows in the view.

Please or to participate in this conversation.