Eager loading pivot table relations

I have a training app that currently has this schema

trainings
id
name

speakers
id
first_name
last_name

training_speaker
id
training_id
speaker_id

training_speaker_dates
id
training_speaker_id
date
time

A training can have multiple speakers, and a speaker can have multiple trainings. Currently, I’m accessing the training speakers by its pivot model.

class Schedule extends Model {
	speakers() {
		return this.belongsToMany('App/Models/Speaker').pivotModel(
			'App/Models/ScheduleSpeaker'
		);
	}
}

Let’s say we have a 3-day training, a speaker can attend the first day and maybe just the morning, that’s why the training_speaker_dates has date and time(morning of afternoon).

Is it possible to also include the training_speaker_dates in the speakers method under Training class?

Or, should I just remove the training_speaker_dates and add the fields to training_speakers table and allow duplicate values (training_id & speaker_id) with unique id? So then it’ll look like this

training_speaker
id
training_id
speaker_id
date
time

Any help would be much appreciated.

1 Like

Needs more time to dig into it but to answer this question:

You can use all kinds of wheres and joins etc in relations

class Schedule extends Model {
  speakers() {
    return this.belongsToMany('App/Models/Speaker')
      .with('training_speaker_dates') // Or use join
      .where({'training_speaker_dates.date':  '2008-01-02'})
      .pivotModel('App/Models/ScheduleSpeaker');
  }
}

Thanks for the answer but how would you define training_speaker_dates method in the model though? I’m getting training_speaker_dates is not defined on Speaker model.

Define training_speaker_dates (trainingSpeakerDates) relation in Speakers model.
Most likely it would be best to use Many Through relation type for it

https://adonisjs.com/docs/4.1/relationships#_many_through