Laravel's pivot table + Pivot table in general

Question

What are laravel pivot tables and pivot tables in general? What is this all about?

Recently I made research about Pivot table. I thought I know them and What they are but then I probably was wrong about that.

I have always thought that a pivot table is just a table that is between two tables (Relation many to many)

But then I started this research and It happened to be not that, but something like different architecture of normal table, where rows are columns. It's changed.

But then Laravel's got pivot tables too. Started reading the documentation and doing research.Maybe I read wrong, but it looks like just pivot table in laravel - table in between two tables, many-to-many.

Searching elsewhere but can't find proper information about it.

Okay, so be it. Laravel's pivot just many to many!

Then I started project and Today I went to the point that making this in-between table as pivot drived me to an Issue, I had a problem with that... minutes and hours... couldn't fix that.

Model was class Room_Student extends Pivot

And what was the fix? Just changing it to class Room_Student extends Model.

I don't think I understand pivot tables anymore and are they different types of pivots? Laravel's pivots are different?

So my question is, what pivot tables really are? + Laravel's pivot tables. Are they different? What is this about?

Please help me understand this.

Solution 1

When learning, focus only the pivot tables concept in Laravel (or eloquent). When I was learning I did not care about the general meaning of pivot table. I focused only on facts in the documentation (https://laravel.com/docs/5.5/eloquent-relationships#many-to-many)

many-to-many relationships require an additional table.And we can insert other useful data to this table as well. And can be used as a model in the system.

Example : User and Roles many-to-many relationship = User_roles

Because of Pivot tables, you can retrieve intermediate table data as a model (like other models in the system).

Example:

//get user by id
$user = App\User::find(1);

//get roles of this user
foreach ($user->roles as $role) {

  //pivot attribute returns a model which represent user_role table
  echo $role->pivot->created_at;

}

NOTE: you can create a class by extending pivot. But you have to implement the correct relationships to make it work. Your code should look somewhat similar to below code.

class Student extends Model
{
    /**
     * The users that belong to the role.
     */
    public function Rooms()
    {
        return $this->belongsToMany('App\Room')->using('App\Room_Student');
    }
}

class Room extends Model
{
    /**
     * The users that belong to the role.
     */
    public function Students()
    {
        return $this->belongsToMany('App\Student')->using('App\Room_Student');
    }
}

class Room_Student extends Pivot
{
    //
}

I hope this helps.

Solution 2

Simply put is a pivot table a table that joins two tables together

say you have a table users

USERS:
user_id, user_name

say you have a table games

GAMES
game_id, game_name

a user can play many games. games have many users playing them.

To link them you make a third table

GAMES_TO_USERS
game_id, user_id

with this table you can request which games a user plays, and which users play which game.

this table GAMES_TO_USERS is in this case the pivot table.

Solution 3

If you know about many-to-many relationships this is common, to handle many-to-many relationships we use intermediate (pivot) table to store relationships of two tables. Ex: consider about “education” and “person” tables which are listed below

table: person

|------|-------|-----|
|  id  | name  | age |
|------|-------|-----|
|  1   | Bob   | 30  |
|  2   | John  | 34  |
|  3   | Marta | 28  |
|------|-------|-----|

table: education

|------|-------|
|  id  | level |
|------|-------|
|  1   | BSc   |
|  2   | MSc   |
|  3   | PhD   |
|------|-------|

Think that Bob has BSc, MSc and John has BSc, MSc, PhD and Marta has BSc, now this is consider as many-to-many relationship and to sort this relationship you need to have intermediate table such as,

table: person_education

|------------|--------------|
|  person_id | education_id |
|------------|--------------|
|  1         |    1         |
|  1         |    2         |
|  2         |    1         |
|  2         |    1         |
|  2         |    3         |
|  3         |    1         |
|------------|--------------|

This table mainly stores the primary keys (IDs) of each relationship. This is the basic idea behind the pivot table and when you use larval there are some best practises such as,

• Name of the pivot table should consist of singular names of both tables, separated by undescore symbole and these names should be arranged in alphabetical order

Laravel Ex:

Class Person extends Model {
    public function education ()
    {   
      return $this->belongsToMany('App\Education', 'person_education');
    }
}

Moreover, you can specify the actual field names of that pivot table, if they are different than default person_id and education _id. Then just add two more parameters – first, the current model field, and then the field of the model being joined

public function education() { 
  return $this->belongsToMany('App\Products', 'products_shops', 'shops_id', 'products_id'); 
}

Solution 4

Keep this in mind

Pivot table — is a table used for connecting relationships between two tables.

Laravel part — laravel provides many-to-many relationship where you can use pivot table, and it is very useful for many cases.

Example:
databases: users, post_user, posts

User.php (Model)

class User extends Model{

  public function posts(){
     return $this->belongsToMany('Post');
  }

}

Now, to access all posts of authenticated user: (view)

@foreach(auth()->user()->posts as $post)
  <li>{{ $post->name }}</li>
@endforeach

Relationship happened:

Remember we have post_user table which is the pivot table we used. If we have:

user_id of 1 and we expect that it is the logged in user and post_id of 1, 2, 3, 4, all this posts will be printed out like so:

|------------|--------------|
|  post_id   |   user_id    |
|------------|--------------|
|  1         |    1         |
|  2         |    1         |
|  3         |    1         |
|  4         |    1         |
|------------|--------------|

Output:

• PostName1
• PostName2
• PostName3
• PostName4

View more solutions

Comments

[Fazle Elahee]

I have found details explanation about a pivots table and laravel. its worth able to check it out. laraveldaily.com/pivot-tables-and-many-to-many-relationships

[Lars Mertens]

Pivot table is just an additional table that probably will occur by having a many to many relationship between 2 data models. It's just a way discovered by all this people that work with databases to connect data with each other and keep an eye at all this best practise rules. It's probably the best method for now. We'll see if this will change in the future. You simply only need indexes with this method and if you studied databases this is a really fast method to lookup data.

[Paul Spiegel]

"Associative tables are colloquially known under many names, including association table, bridge table, cross-reference table, crosswalk, intermediary table, intersection table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, pivot table (as used in Laravel - not to be confused with pivot table (spreadsheets)), or transition table." - en.wikipedia.org/wiki/Associative_entity

[ejntaylor]

Think the pivot table should be named role_user because they are required to ordered alphabetically...

[Henry]

What if your table has more than one many to many relationships? Does Eloquent just pick the first one it finds?