Laravel three table pivot

Question

Music Discovery website

Summary

I am using Laravel to create a music search/discovery website. In short Artists have many Albums which have many Tracks. A User has many Tags associated with the Tracks. TAGGING CAN ONLY BE DONE ON TRACKS, NOT ALBUMS OR ARTISTS.

A user visits the website and can search for a set of tags e.g. "Happy", "Party", "School". A list of tracks is returned along with some other details.

The tracks are tagged in the first place by users. A user should be able to see all the tracks that they have tagged and be able to search/ filter based on tags too.

The Issue

With my current setup (see table structure below) there is no way to tell which user tagged a particular track. I am thinking about merging the two tables tag_track and tag_user into one. The new table would have a structure of:

Tag_Track_User table

Tag_ID
Track_ID
User_ID

My Questions

1. If I do this, what relationships do I set up on the models
2. How can I get all tracks associated with a tag (tags.text)

Current Table Structures

Users table

User_ID (UNI)
User_Name

Artists table

Artist_ID (UNI)
Artist_Name

Album table

Album_ID (UNI)
Album_Title
Artist_ID

Track table

Track_ID (UNI)
Track_title
Album_ID

Tag table

Tag_ID (UNI)
Tag_text (UNI)

Tag_Track table

Tag_ID
Track_ID

Tag_User table

Tag_ID
User_ID

Model Structures

Artist model

Has many Tlbums

Album model

Belongs to Album Has many Tracks

Track model

Belongs to Album Has many Tracks

User model

Belongs to many Tags

Tag model

Belongs to many Tracks Belongs to many Users

Answer 1

You're right about the pivot table. It must contain 3 keys, as in this situation tags can't exist without a user or a track. So it's a relationship between 3 models.

So here's all you need (I'm skipping artist and album relations ofc):

// pivot table tag_track_user: tag_id, track_id, user_id, id, timestamps

// Tag model
public function users()
{
    // by default only 2 keys are on the pivot, so lets add the third
    return $this->belongsToMany('User', 'tag_track_user')->withPivot('track_id'); 
}

public function tracks()
{
    return $this->belongsToMany('Track', 'tag_track_user')->withPivot('user_id'); 
}
------------
// Track model
public function tags()
{
    return $this->belongsToMany('Tag', 'tag_track_user')->withPivot('user_id'); 
}

public function users()
{
    return $this->belongsToMany('User', 'tag_track_user')->withPivot('tag_id'); 
}
------------
// User model
public function tags()
{
    return $this->belongsToMany('Tag', 'tag_track_user')->withPivot('track_id');
}

public function tracks()
{
    return $this->belongsToMany('Track', 'tag_track_user')->withPivot('tag_id'); 
}

Now, with this setup you can get everything you want:

$user->tags->first()->users; // other users who used given tag
Tag::where('name','happy')->first()->tracks; // all tracks tagged 'happy'
$user->tracks()->where('title','Happy')->first()->tags; // all tags attached to song Happy
$user->tags->load('tracks')->fetch('tracks')->collapse(); // array of all the tracks related to all tags of a user (as arrays not models)
etc.

Of course the above will cause a few db queries, but that's normal.