Add a parameter to your sql query-CodePudding

here is the problem I am currently facing. My goal is to add exercises from a database with in predefined programs before. I have made an sql query that allows not to add a duplicate exercise in the program. And the problem I have is that in the sql query, my program cannot take the id of a program that is in the parameters of my function.

My controller containing my function to retrieve the exercises that are or are not in the program

Public function GetExercicesFromBDD($id) {    
    $leProgramChoisie = new ExerciceModel();
    $leProgramChoisie = $leProgramChoisie->GetProgramById($id);

    $leProgram = DB::table('ProgramToExercice')->where('IdProgram', '=', $id)->get();


      $mesExercices =DB::table('Exercice')
      ->leftjoin('ProgramToExercice', function ($join) {
        $join->on('ProgramToExercice.IdExercice', '=', 'Exercice.Id')
         ->Where('ProgramToExercice.IdProgram' ,'=', $id );

        })
        ->whereNull('ProgramToExercice.IdProgram')
      ->get();

      dd($mesExercices);


      return view('addExerciceIntoProgram', ['mesExercices'=>$mesExercices, 'IdProgram'=>$id, "leProgramChoisie" => $leProgramChoisie]);
      }

My model to get the program id

   public function GetProgramById($id) {
         $leProgram = DB::table('ProgramToExercice')->where('IdProgram', '=', $id)->get();
         return $leProgram;
    
     }

my view containing the button to add exercises with its route

 @foreach ($programs as $program)
    <form action={{url("Program/" . $program->Id . "/editExercice")}} method="post">
                            @csrf
                        <button type="submit" >Ajouter des exercices dans un programme</button>
                        </form>

CodePudding user response：

The anonymous function is not aware of $id outside its scope, so need to pass that in with use:

  $mesExercices =DB::table('Exercice')             // pass the $id 
  ->leftjoin('ProgramToExercice', function ($join) use ($id) {
    $join->on('ProgramToExercice.IdExercice', '=', 'Exercice.Id')
     ->Where('ProgramToExercice.IdProgram' ,'=', $id );

    })
    ->whereNull('ProgramToExercice.IdProgram')
  ->get();