Aug 4, 2017

Level 1

What is the best solution for 3 tables many-to-many

Hello, i want to do something like that:

(Teachers and Students are in Users table)

Many Students can have many Classes depending on the year

| User  | Class | Year |
| ------- | ----- | ------- |
| John  | 1  | 2016  |
| John  | 2  | 2017  |
| John  | 1  | 2018  |
| Doe  | 1  | 2016  |
| Doe  | 1  | 2017  |
| ...  | ...  | ...  |

Many Teachers can have many Classes depending on the year

| User  | Class | Year |
| -------- | ----- | ------- |
| Mr Smith  | 1  | 2016  |
| Mr Smith  | 2  | 2016  |
| Mr Smith  | 3  | 2016  |
| Mr Smith  | 1  | 2017  |
| ...  | ...  | ...  |

So, many years have many students and classes

I want to be able to get for example:

a list of students of Mr Smith for year 2016
a list of students of class #4 for year 2016
...

Should I make pivot table with 3 tables like : class_user_year ?

Or i can make that with manytomany relations, with 2 pivot tables like :

User model

public function classes(){
    return $this->hasMany('App\Classe', 'class_user', 'user_id', 'class_id');
}
public function years(){
    return $this->hasMany('App\Year', 'year_user', 'user_id', 'year_id');
}

Class model

public function users(){
    return $this->hasMany('App\User', 'class_user', 'class_id', 'user_id');
}
public function years(){
    return $this->hasMany('App\Year', 'class_year', 'class_id', 'year_id');
}

Year model

public function users(){
    return $this->hasMany('App\User', 'year_user', 'year_id', 'user_id');
}
public function classes(){
    return $this->hasMany('App\Classe', 'class_year', 'year_id', 'class_id');
}

Thanks for your time. Som

36864

8 years ago

Level 13

A simplified schema would let you do the things you need with relative ease

a list of students of Mr Smith for year 2016

User::where('name', 'Mr Smith')->load('classes.users')->classes()->wherePivot('year', 206)->get()->pluck('users')->flatten();

a list of students of class #4 for year 2016

Class::find(4)->load('users')->users()->wherePivot('year', 2016)->get();

In this scenario, year isn't actually a model in your schema, it's just an attribute on your pivot table.

The downside of this is it's not easy to determine who's teaching a class, and if you allow teachers to also be students, you're gonna run into all sorts of problems.

I'll try to come up with a better schema if I can find the time.

1 like

Somesa

8 years ago

Level 1

Thanks a lot for your rapid answer i try that !

36864

8 years ago

Level 13

Oh, I completely missed it, but you've defined your relations wrong.

You should be using BelongsToMany for many-to-many relations. https://laravel.com/docs/5.4/eloquent-relationships#many-to-many

1 like

Somesa

8 years ago

Level 1

Okay thanks a lot !!

Somesa

8 years ago

Level 1

I changed my relations to belongsToMany, but i still have a problem to get a list of students of Mr Smith for year 2016

I can now get the classes of mr smiths for 2016 :

$classes = User::where('id', 1)->first()
            ->classes()
            ->wherePivot('year', 2016)
            ->get();

when i add

->pluck('users')->flatten()

This returns many users, even those who do not study this year

do you think that i should separate students and teachers in different tables ?

thank you very much

Please or to participate in this conversation.