Has Many Through Pivot

Hi,

I currently have 3 tables drivers, Fieldtrips and their pivot, driver_fieldtrip. I.e. the drivers can have many fieldtrips and a fieldtrips can have many many drivers.

Within the pivot (driver_fieldtrip) it has 6 additional fields (straight_hours, straight_hours_bank, time_half_hours, time_half_hours_bank, double_hours, double_hours_bank).

I want to move these out of the pivot and into their own table (hours) this would alter it to have (id, driver_fieldtrip_id, hours, bank, multiplier i.e. 1, 1.5, 2)

It is my understanding this would be a hasMany off the pivot table and the hours table would be a belongsTo. However, this is where I am getting lost, do i create a model for the pivot table and the hours table. Or would i use a hasManyThrough() to retrieve the data on the hours table. Or, am I simply getting to all wrong and my structure is not correct?

Thanks

JarekTkaczyk

11 years ago

Best Answer

Level 53

@kieranjfahy First off hasManyThrough works for 3 models, you cannot use it without defining both far model and through model.

Next, if you want separate table for hours, then you won't use belongsToMany to get them in any way, because you can't eager load any relations on the Pivot model. You can do it on each pivot, but it's cumbersome.

That said, you will have:

Driver belongsToMany FieldTrip
FieldTrip belongsToMany Driver

// DriverTrip is the model for pivot table, but NOT Pivot model
Driver hasMany DriverTrip
FieldTrip hasMany DriverTrip
DriverTrip hasMany Hour

Driver hasManyThrough Hour, DriverTrip
FieldTrip hasManyThrough Hour, DriverTrip

This might be a little bit inconvenient, so I suggest you reconsider this appraoch. Do you really need that separate table? Why?

And what are those columns hours_something.. referring to?

kieranjfahy

11 years ago

Level 16

So, it's my understanding, in this case I would not use a pivot table I would simply have a DriverTrip table. In that case could I not simply create Models for DriverTrip and Hours tables?

The columns hours_something... relate to what a driver does on a field trip, so for example most drivers would be paid a time and a half so they would write the hours into that column and mark this as either bank the hours to take latter or get paid out for them. However, if a driver is sat waiting this is paid at straight time which again they can choose to bank or pay out, also after 2 hours on the weekend the rate becomes double time. This means they could have a combination of of time and a half, straight time and double, which they could choose to bank.

If I don't separate them out I will end up with 1000's of empty columns within my database. where a driver has only done time and half hours, no double and no straight. Hope this make sense?

JarekTkaczyk

11 years ago

Level 53

@kieranjfahy Sure, you don't need to use it as pivot at all, so no belongsToMany will be used.

However, you can still use this relationship, it doesn't affect the whole setup. It's up to you and your needs during the development - you may find it useful in some cases, but that might never happen as well :)

kieranjfahy

11 years ago

Level 16

Thanks @JarekTkaczyk I do believe this is the direction I will take, the simple fact this way I can avoid wasted space.

Please or to participate in this conversation.