Back

Many to many with additional columns / FK's


Hi there,

Since a few months i'm working with Laravel and i love it, came from codeigniter.

I have a question based on the ORM of laravel with many to many.

I have three tables:

  • posts
  • users
  • post_user

But in the post_user i want additional data, so i have:

  • user_id (sender )
  • post_id
  • list_id ( in which list the post was sended )
  • user_id (receiver)
  • status ( accept of denied by receiver )

Normally you combine only the user_id and the post_id. How do i handle the other two? Maby its not a Laravel solution but a database one, so please let me know it that might be.

Thnx allready for the effort!

crhayes replied 3 years ago

The table 'post_user' is called a "pivot table" and it links the many-to-many relationship between users and posts. Are you asking how to get data off of the pivot table? If so, read this section of the documentation: http://laravel.com/docs/eloquent#working-with-pivot-tables

Regarding your schema for the post_user table you'll want something like this:

post_id
sender_user_id
receiver_user_id
list_id
status

If you're looking to have multiple columns combined as keys (called composite keys), read this section of the documentation: http://laravel.com/docs/schema#adding-indexes

You can add composite keys with the following syntax:

$table->primary(array('field_1', 'field_2'));

longilineo replied 3 years ago

Create a user2 table with id and name fields. Create post2 table with id and content fields. Create a pivot table with following fields: id, sender_id, receiver_id, post_id, status. Insert some records in user2 and post2. Play with this example:

User2 model:

<?php

class User2 extends Eloquent{
	protected $table = "user2";
	public $timestamps = false;
	protected $guarded = array("id");
        
	public function sent(){
		return $this->belongsToMany("Post2", "post2_user2", "sender_id", "post_id")
 			->withPivot("status", "receiver_id")
 			->whereSenderId($this->id);
	}
	
	public function received(){
		return $this->belongsToMany("Post2", "post2_user2", "receiver_id", "post_id")
 			->withPivot("status", "sender_id")
 			->whereReceiverId($this->id);;
	}
	
}

Post2 model

<?php

class Post2 extends Eloquent{
	protected $table = "post2";
	public $timestamps = false;
	protected $guarded = array("id");
	
	public function senders(){
		return $this->belongsToMany("User2", "post2_user2", "post_id", "sender_id")
		->withPivot("status", "receiver_id");
	}

	public function receivers(){
		return $this->belongsToMany("User2", "post2_user2", "post_id", "receiver_id")
		->withPivot("status", "sender_id");
	}
	
}

routes.php

<?php

Route::get("/strangeRelations", function(){
	
	$u1 = User2::find(1);
	$u2 = User2::find(2);
	$p = Post2::find(1);
	$p2 = Post2::find(2);
	
	
	$u1->sent()->attach($p->id, array("receiver_id"=>$u2->id, "status"=>1));
	$p2->senders()->attach($u2->id, array("receiver_id"=>$u1->id));
	

	echo $u1->sent->toJson();
	echo $u2->received->toJson();
});

See what appen in db reaching "/strangeRelations", hope it should help you to achieve your goal.

mmolenaar1 replied 3 years ago

@crhayes thnx but not exactly what i mean. I know how many to many works, also understand the meaning of pivot.

@longilineo looks good, and i think its what i mean. But, theres always a but ;) This one requires a lot of database requests. Isnt it possible with ORM to left join? In pure mysql its possible to retrieve all the information at once. Im basically knew to laravel ORM so dont shoot me if im wrong.

Like this..

SELECT posts.*, sender.*, receiver.* lists.*
FROM posts 
LEFT JOIN post_user ON posts.id = post_user.postsID 
LEFT JOIN users AS sender ON sender.id = sender_usersID 
LEFT JOIN users AS receiver ON receiver.id = receiver_usersID
LEFT JOIN lists ON fwd_user.listID = lists.id

EDIT:

I see i can use JOIN on a pivot, so that will fix my problem i guess :)

Kovah replied 8 months ago

For any other users that are searching for additional information about this topic for Laravel 5.x: I found a very helpful article about this on laraveldaily.com: http://laraveldaily.com/pivot-tables-and-many-to-many-relationships/


Sign in to participate in this thread!


We'd like to thank these amazing companies for supporting us