0

I am trying to query a pivot table to show how many exercises have one day, but there is something wrong with that.

I need to get all the exercises by Monday or any day.

I have three tables: routines, exercises and exercise_routine.

routines     exercise_routine  exercise
--------     ----------------  --------
id           id                id
name         routine_id        name
description  exercise_id       description
user_id      week_day_id
             sets
             reps

I would like to get all the exercises by week_day_id, hope you understand my problem.

I tried these examples from other people on stackoverflow but it does not work.

Exercise::whereHas('routines', function($q) {
    $q->where('routines.week_day_id', 1);
})
->get();
return \DB::table('exercises')
->join('exercise_routine', 'exercise_routine.exercise_id', '=', 'exercises.id')
->where('exercise_routine', 1)
->get();
dd( DB::table('exercises')
->where('exercises.id', '=', 1)
->select('exercises.id'));
// Routine Model
public function exercises()
{
    return $this->belongsToMany('App\Models\Exercise');
}
// ExerciseModel
public function routines()
{
    return $this->belongsToMany('App\Models\Routine')->as('plan')->withPivot('sets', 'reps', 'week_day_id')->withTimestamps();
}
// Controller
public function show(WeekDay $weekday)
{
    Exercise::whereHas('routines', function($q, $weekday) {
        $q->where('routines.week_day_id', $weekday);
    })
    ->get();
}
// api routes
Route::group(['prefix' => '/{weekday}/exercises'], function () {
    Route::get('/', 'WeekDayExerciseController@show')->middleware('auth:api');
});

I expected to get all the exercises by Monday for example like this:

{
    "id": 236,
    "name": "Upright Row (Barbell)",
    "description": "Description for Upright Row (Barbell)"
},
{
    "id": 237,
    "name": "Upright Row (Cable)",
    "description": "Description for Upright Row (Cable)"
},
{
    "id": 238,
    "name": "Upright Row (Dumbbell)",
    "description": "Description for Upright Row (Dumbbell)"
},
IGP
  • 14,160
  • 4
  • 26
  • 43
desancheztorres
  • 353
  • 1
  • 6
  • 13
  • Only laravel calls junction tables as pivot tables. Pivot tables tag here on SO refers to the generally understood meaning of pivot tables. Pls always read the tag guidance before using a tag. – Shadow Aug 28 '19 at 08:45

2 Answers2

1

In your Controller you've got a slight error in your closure

// Controller
public function show(WeekDay $weekday)
{
    Exercise::whereHas('routines', function($q) use ($weekday) {
        $q->where('week_day_id', $weekday->id);
    })
    ->get();
}
IGP
  • 14,160
  • 4
  • 26
  • 43
1

You are wrong here

Exercise::whereHas('routines', function($q) {
    $q->where('routines.week_day_id', 1);
})
->get();

Because week_day_id column isn't on routines table,

You need to query pivot table using wherePivot method in this way

Exercise::whereHas('routines', function($q) {
    $q->wherePivot('week_day_id', 1);
})
->get();

see: https://laravel.com/docs/master/eloquent-relationships#many-to-many

Zohaib
  • 588
  • 1
  • 6
  • 23