Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

charlie@skillpill.com's avatar

3 foreign keys in a pivot table, but slightly more complex relationship

Hi there,

I currently have the following 3 models:

  • Showcase
  • Tag
  • Video

A video has many tags. A tag has many videos. A showcase has many videos. A showcase has many tags. A tag belongs to many showcases. A video belongs to many showcases.

The complexity is that we do not necessarily want a video to show up within a certain tag within a certain showcase, even though it 'belongs' to that tag.

For example, take this scenario. A video is tagged with "TagA" and "TagB". A showcase contains tags "TagA", "TagB", "TagC". We only want the video to be showing in "TagA" for this showcase, and maybe in both "TagA" and "TagB" in a completely different showcase.

In my pre-framework days, this was handled with a showcase_tag_video table with 3 foreign_ids, and awkward database queries:

id --- showcase_id --- tag_id --- video_id --- added_at --- etc

I cannot simply use pivot tables between showcase-video, tag-video and showcase-tag. That would give too much data I think.

How would this more complex table work in terms of Eloquent/Laravel? Will it just be a model with specific queries to return all videos for a given showcase, and all videos for a given tag?

Thanks.

0 likes
2 replies
Shahrukh4's avatar

Well as per the standard way your pivot table has only two columns i.e. both table id's, but if you want to do this, laravel provide you flexibility.

just provide the info in your methods in models which key you want to use when fetching the data i.e lets say you have three tables Users, Posts, Comments. and want to connect all of them in one pivot table then do as follows

class User extends Authenticatable{
//when matching user_id and post_id
public function posts(){
    return $this->hasMany('App\Posts',  'post_id');
}

//when matching user_id and comment_id
public function comments(){
     return $this->hasMany('App\Comments',  'comment_id');
}
}

keyboardSmasher's avatar

Huh? in posts() hasMany will look for a post_id on the post table, which obviously doesn't exist.

There is no pivot table in your example.

Please or to participate in this conversation.