How do i define this type of relationship ?

This is what I'm understanding for your data structure TrackType 1 -> ['Track 1', 'Track 2', 'Track 3'] TrackType 2 -> ['Track 4', 'Track 5', 'Track 6']

Track 1 -> ['Subgenre 1', 'Subgenre 2'] Track 2 -> ['Subgenre 2', 'Subgenre 3']

Correct?

If that's the case then the TrackType model would have a hasMany relationship to the Track model and the inverse would be the Track model belongsTo the TrackType model. This would be similar to a unique Post (TrackType) has many Comments (Tracks) http://laravel.com/docs/5.2/eloquent-relationships#one-to-many

Then the Tracks each can have multiple Subgenres, and the Subgenres can be shared between the tracks. This would be similar to User 1 and User 2 having multiple user roles where User 1 has the roles "SuperAdmin" and "User" and User 2 has the roles "Moderator" and "User." This is a Many to Many relationship http://laravel.com/docs/5.2/eloquent-relationships#many-to-many

To find the Tracks associated with the TrackType and specific subgenre name would be queried as such, I believe... $tracks = App\TrackType::with('tracks.subgenres')->where('trackTypeName', 'trackTypeNameHere')->where('subgenreName', 'sgNameHere')->get();

Thanks @kenmoini your suggestion worked for but there are some small issues with it .

i tried this ( Eager Loading )

App\TrackType::with(['tracks.subgeneres' => function ($query) {
        $query->where('name', 'Trans');
    }])->where('name', 'Single')->get();

then i dumped the Query . this is what i got

array:2 [▼
  0 => array:3 [▼
    "query" => "select * from `tracktype` where `name` = ?"
    "bindings" => array:1 [▼
      0 => "Single"
    ]
    "time" => 0.51
  ]
  1 => array:3 [▼
    "query" => "select * from `tracks` where `tracks`.`id` in (?)"
    "bindings" => array:1 [▼
      0 => 1
    ]
    "time" => 0.48
  ]
]

That second Query is totally unnecessary for me . instead i want to get the tracks with the tracks that have TrackType of Single and Subgeneres with Trans . how do i get rid of that second query and filter through all my tracks which only contains a specific subgenre ID ?

By the way nice Explanation . Appreciate it alot :)

How many tracks do you have associated to the subgenre Trans? That looks like the correct query structure.

No. i have 2 tracks

Both assigned to the subgenre Trans?

There are 2 assigned for the Trans. And what i noticed is that i have tracks starting with ID 3 and goes on ( removed first 2 ) . But here the 2nd Query tries to get the Track associated with the ID 1 which is not correct . is there anything wrong with the Query ? im out of options now :/

"Instead i want to get the tracks with the tracks that have TrackType of Single and Subgeneres with Trans ."

$tracks = App\Tracks::with(['trackType' => function ($query) {
        $query->where('name', 'Single');
    }, 'subgenre' => function ($query) {
        $query->where('name', 'Trans');
    }])->get();

Maybe?

it grabs all the records on the Tracks table without respecting the where clauses . i might have to change my tables right ?

$tracks = App\Tracks::whereHas(['trackType' => function ($query) {
        $query->where('name', 'Single');
    }, 'subgenre' => function ($query) {
        $query->where('name', 'Trans');
    }])->get();

Maaaybe? (I'm winging this myself here, don't have a data structure similar to yours to play with interactively)

Subgeneres table

        Schema::create('subgenres', function(Blueprint $table) {
            $table->increments('id');
            $table->integer('genre_id')->unsigned();
            $table->foreign('genre_id')->references('id')->on('genres')->onDelete('cascade');
            $table->string('name');
        });

Subgenere and tracks (Pivot)

        Schema::create('subgenre_track', function (Blueprint $table) {
            $table->integer('subgenre_id')->unsigned()->index();
            $table->foreign('subgenre_id')->references('id')->on('subgenres')->onDelete('cascade');
            $table->integer('track_id')->unsigned()->index();
            $table->foreign('track_id')->references('id')->on('tracks')->onDelete('cascade');
            $table->primary(['subgenre_id', 'track_id']);
        });

TrackType table

        Schema::create('tracktype', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
        });

TrackType and Tracks table (Pivot)

        Schema::create('track_tracktype', function (Blueprint $table) {
            $table->integer('track_id')->unsigned()->index();
            $table->foreign('track_id')->references('id')->on('tracks')->onDelete('cascade');

            $table->integer('tracktype_id')->unsigned()->index();
            $table->foreign('tracktype_id')->references('id')->on('tracktype')->onDelete('cascade');

            $table->primary(['track_id', 'tracktype_id']);
        });

Just wanted to give you a quick idea what my structure looks like

@kenmoini .. It worked =D .. Thanks man . I mean for all the support (Y)

All i did was this ( Changed the RelationShip to this .. i changed it to hasMany before )

    /**
     * Gets the tracks lists Accociated with a track type
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function tracks()
    {
        return $this->belongsToMany('App\Tracks', 'track_tracktype', 'tracktype_id', 'track_id');
    }

and the Query was this

    $track = App\TrackType::with(['tracks.subgenres'], function($query) {
        $query->where('name', 'Trans');
    })->where('name', 'Mashup')->get();

but there are results from Dubstep too .. Any suggestiones for that ?

A Tracktype has many tracks and a track can have many subgenres . but a track can only have one track type

Why pivot tables between types and tracks ?

• Type hasMany Track
• Track belongsTo Type
• Subgenre belongsToMany Track
• Track belongsToMany Subgenre

Which means tracks has the type_id and there is a pivot table between tracks and subgenres.

Yes . Your are Correct @pmall . What do you suggest ?

The suggestion is in the post above

yes. i understood what you suggested . i tried several times but no luck . if you have time and if you can , kindly show me the code ? im really out of options now :/

@pmall are you suggesting me to add column to the Track table to contain the TrackType ID ?