Get rows by column in Laravel Many to Many relationship

Question

Laravel Many to Many relation

Hi, I am stuck trying to get the data from database by weeks.

I have the next table (Many to Many relation)

1. routine: id, plan_id, user_id.
2. exercise: id, name, description, image.
3. exercise_routine: id, routine_id, exercise_id, week, day.

Relations

Routine

public function exercises() {
    return $this->belongsToMany(Exercise::class)->withPivot('week', 'day', 'completed')->orderBy('week','asc');;
}

Exercise

public function routines() {
    return $this->belongsToMany(Routine::class)->withPivot('week', 'day', 'completed');
}

I would like to get all the rows by week like this.

    Json example:
    {
      "week": [
        1: {
         "exercises": [
           {
             "name": "Abs"
           }
         ]
        },
        2: {
         ...
        }
      ]

    }

I already tried this

 if(!empty($routine)) {

            foreach ($routine->exercises as $exercise) {
                $week = $exercise->pivot->week;

                if($week == $previous_week) {
                    array_push($this->weeks, $exercise->pivot->week);
                } else {
                    array_push($this->weeks, [
                        $exercise->pivot->week => $exercise->pivot->week
                    ]);
                }

                $previous_week = $exercise->pivot->week;

            }

//            dd($this->weeks);

//            return DB::table('exercise_routine')->where('routine_id',$routine->id)->max('week');
        }

The explanation

exercise_routine table has a week number to separate exercises by week. I need to create an array and if the week is 1, then push in the array 1, if the next is 1, push in the same array. If the next is 2 or different just push but in the number 2.

I am not sure if you can understand what I mean, it is just trying to get the way to do it.

Many thanks in advance.

Answer 1

Using the collection method mapToGroups can help you simplify grouping up the exercises.

Here's an example on how I would use it for your case:

if(!empty($routine)){
  // creating groups of exercise by week [1 => ['abs'], 2 => []]
  $exerciseWeeks = $routine->exercises->mapToGroups(function($exercise, $k){
      return [$exercise->pivot->weeks => $exercise->name];
    })
    ->map(function($exerciseInWeek, $k){ // creating your json format from here point on
      $weekObj = new \stdClass;
      $weekObj->exercises = [];
      foreach($exerciseInWeek as $exerciseName){
        $exerciseObj = new \stdClass;
        $exerciseObj->name = $exerciseName;
        $weekObj->exercises[] = $exerciseObj;
      }
      return $weekObj;
    });

  // creates the final json object
  $routineObj = new \stdClass;
  $routineObj->week = $exerciseWeeks->all();

  $finalRoutineJSON = json_encode($routineObj);
}