Laravel - DB Query items based on pivot table status-CodePudding

I have in my app a list of modules that players are able to complete, and when they do, they have to stop appearing in the "available modules" list. I have the table "players", "modules" and "module_player" to save the relation.

So, for the "available modules" list I only want to show modules that haven't been started yet (aka no record on the module_player table) or have been started but not finished yet (aka status 1 in module_player table).

Lets say I have:

players table

id	name
1	Player_1
2	Player_2

modules table

id	name
1	Module_1
2	Module_2
3	Module_3

module_player table

module_id	player_id	status
1	1	1
2	1	2

The "available list" should return only the module 1 (started but not finished) and the module 3 (not started).

Problem is I'm only getting the module 1 in the "available list", this is the query I have with obviously has something wrong in it:

        $modules = \DB::table('modules')
            ->join('module_player', 'modules.id', '=', 'module_player.module_id', 'left')
            ->join('players', 'module_player.player_id', '=', 'players.id', 'left')
            ->where('module_player.status', '!=', 2)
            ->where('module_player.player_id, '=', 1)
            ->select('modules.id', 'modules.name', 'modules.image', 'modules.mandatory')->paginate(10);

Thanks

CodePudding user response：

As you are using

`where('module_player.player_id, '=', 1)`

You will get only records where player_id is 1 so try the below query with whereNotIn

$modules = \DB::table('modules')
                ->join('module_player', 'modules.id', '=', 'module_player.module_id', 'left')
                ->join('players', 'module_player.player_id', '=', 'players.id', 'left')
                ->whereNotIn('modules.id', function($query){
                     $query->select('module_id')
                     ->from('module_player')
                     ->where('status', 2);
                })
                ->select('modules.id', 'modules.name', 'modules.image', 'modules.mandatory')->paginate(10);

if you want it for any particular user you can pass player_id in use()

->whereNotIn('modules.id', function($query) use($player_id){
                         $query->select('module_id')
                         ->from('module_player')
                         ->from('player_id', $player_id)
                         ->where('status', 2);
                    })

CodePudding user response：

remove ->where('module_player.player_id, '=', 1) line as it will return records with player_id 1. and try below query

$modules = \DB::table('modules')
            ->join('module_player', 'modules.id', '=', 'module_player.module_id', 'left')
            ->join('players', 'module_player.player_id', '=', 'players.id', 'left')
            ->where('module_player.status', '!=', 2)
            ->select('modules.id', 'modules.name', 'modules.image', 'modules.mandatory')->paginate(10);