[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Omaha.pm] DBIx::Class - join on a joined table



Whoah, neat. I just did my first "join on a joined table" in DBIC. It guessed at the syntax and it "Just Worked."   :)

   prefetch => [ qw( program ), { exercise => 'instruction' } ],

   SELECT ... FROM program_details me JOIN programs program ON program.id = me.program_id JOIN exercises exercise ON exercise.id = me.exercise_id JOIN instructions instruction ON instruction.id = exercise.instruction_id WHERE ...

I gained 2 XP for that one. Full context below.   :)

Jay Hannah
Software Architect
jhannah@mutationgrid.com | http://mutationgrid.com | 1-402-598-7782




   my $rows =    $c->model('amd::ProgramDetail')->search(
      {
         program_id     => $program->id,
         workout_number => $workout_number,
      },
      {
         prefetch => [ qw( program ), { exercise => 'instruction' } ],
         order_by => { -asc => [ qw( workout_number exercise_number ) ] },
      }
   );


export DBIC_TRACE=1

SELECT me.id, me.program_id, me.workout_number, me.exercise_number, me.exercise_id, me.extype, me.sets, me.reps, me.style, me.step, me.worktype, me.primex, me.primperc, me.compperc, me.percstop, me.targethigh, me.targetlow, me.highset, me.lowset, me.valinc, me.valdec, program.id, program.short_desc, program.skill_level, program.category, program.long_desc, exercise.id, exercise.short_desc, exercise.instruction_id, exercise.long_desc, instruction.id, instruction.long_desc FROM program_details me JOIN programs program ON program.id = me.program_id JOIN exercises exercise ON exercise.id = me.exercise_id JOIN instructions instruction ON instruction.id = exercise.instruction_id WHERE ( ( program_id = ? AND workout_number = ? ) ) ORDER BY workout_number ASC, exercise_number ASC: '63', '1'