Alexd2 Alexd2 - 1 month ago 13
PHP Question

Laravel 5.1 - pivot table between three tables, better option?

I have 3 tables: Users, Languages and Levels.

One user have many languages and level.

Example:


  • Jorge speaking: English advanced, Spanish basic.

  • Peter speaking: English advanced, Italian medium, French basic.

  • Luis speaking: Spanish advanced, English advanced, Italian medium.



Table User

+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| lastname | varchar(50) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+


Table Languages

+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+


Table levels

+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+


Pivot table

+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| language_id | int(11) | NO | | NULL | |
| level_id | int(11) | NO | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+-------------+----------+------+-----+---------+----------------+


The question is, how create the pivot table with three tables (in the doc laravel the example is only with 2 tables) is possible with three tables?
How to relationship with eloquent?

Answer

Well I have something in a project using three tables and as pivot, so I run:

Modelo User:

class User extends Model{

public function levels(){
return $this->belongsToMany('App\Level','name pivot table')->withPivot('language_id');
}

public function languages(){
return $this->belongsToMany('App\Language','name pivot table')->withPivot('level_id');
}
}

Modelo Levels:

 class Level extends Model{

    public function users(){
    return $this->belongsToMany('App\User','name pivot table')->withPivot('language_id');
    }

    public function languages(){
    return $this->belongsToMany('App\Language','name pivot table')->withPivot('user_id');
    }
    }

Table Language:

    class Language extends Model{

    public function users(){
    return $this->belongsToMany('App\User','name pivot table')->withPivot('level_id');
    }

   public function levels(){
    return $this->belongsToMany('App\Level','name pivot table')->withPivot('user_id');
    }
    }

I hope you serve this option that I use to access information from all three tables

use:

$user = User::find(1);
$user->levels->pivot->language_id;
$user->levels->name;
$user->languages->pivot->level_id;
$user->languages->name;