Framework: [Proposal] Ability to map pivot table columns.

[trq]

I currently have a use case where I need to create a many to many relationship that joins back onto the same table.

With two tables involved the pivot table would normally be created with the columns, _id and _id, in this case however, they are both pointing to the same table.

My model currently looks like:

<?php

class Foo extends Eloquent {
  public function foos() {
    return $this->belongsToMany('Foo');
  }
}

What I envisage this looking like is not too different to how we currently map different columns, eg;

return $this->belongsToMany('Foo', 'foos', ['foo_id' => 'parent_id', 'foo_id' => 'child_id']);

Where foo_id is the fk & other key from the foos table and they are being mapped to parent_id & child_id within the pivot.

Hope this makes sense.

[darrennolan]

:+1:

I think what @trq means is that his Pivot table is going to link back to his original table, so it's not possible to have the same column name twice.

Main Table Foos
| Id  | Info1         | Info2 |
| --- | ------------- | ----- |
| 1   | some info1    | $12   |
| 2   | more info     | $13   |
| 3   | somethingelse | $99   |

Foos Pivot
| foos_parent_id  | foos_child_id |
| --------------- | ------------- |
| 1               | 3             |
| 2               | 3             |
| 3               | 100           |

[franzliedke]

return $this->belongsToMany('Foo', 'foos', ['foo_id' => 'parent_id', 'foo_id' => 'child_id']);

That will not work, though. You can't have the same key twice in an array (the second one would just overwrite the first). I guess parameter order would then have to be what distinguishes the two.

[trq]

Sorry, that's just a typo. It should probably be more like:

return $this->belongsToMany('Foo', 'foos', ['foo_id' => 'parent_id'], ['foo_id' => 'child_id']);

[franzliedke]

Then why the arrays and why the keys?

On Wed, Feb 27, 2013 at 2:37 AM, Tony R Quilkey [email protected]:

Sorry, that's just a typo. It should probably be more like:

return $this->belongsToMany('Foo', 'foos', ['foo_id' => 'parent_id'], ['foo_id' => 'child_id']);

—
Reply to this email directly or view it on GitHubhttps://github.com/laravel/framework/issues/441#issuecomment-14151143
.

[taylorotwell]

This should already be possible?

[taylorotwell]

Here is the signature to the belongsToMany method. You can use whatever key names you want, and joining back onto the same table is indeed possible:

public function belongsToMany($related, $table = null, $foreignKey = null, $otherKey = null)

[darrennolan]

But will this not lead to problems when the column references are the same? I'm going to have to do some tests for this specifically.

[trq]

This signature only allows you to define the names of the columns within the tables you are trying to join no? I need to be able to also be able to define the names of the columns within the pivot table itself.

The issue is, if you try and join a table back to itself within a many to many relation currently your pivot table would need two columns called _table__id. Obviously,you can't have two columns within the same table with the same name.

[taylorotwell]

This already works, guys. You can name the columns in your pivot table whatever you want. A classic example of this type of behavior is a user "friends" relationship which joins back to the user table itself. Here's an example of what it would look like:

Pivot Table:
user_id
friend_id

class User {

    public function friends()
    {
     return $this->belongsToMany('User', 'user_friends', 'user_id', 'friend_id');
    }

}

[marco-solare]

pretty cool, built a deadly simple friend relation implementation around this.

migration:

public function up()
{
    Schema::create('friend_user', function(Blueprint $table) {
        $table->increments('id');
        $table->integer('friend_id')->unsigned()->index();
        $table->integer('user_id')->unsigned()->index();
        $table->foreign('friend_id')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}

user model (class):

// friends
public function friends()
{
    $friends = $this->belongsToMany('User', 'friend_user', 'user_id', 'friend_id');
    return $friends;
}
public function add_friend($friend_id)
{
    $this->friends()->attach($friend_id);   // add friend
    $friend = User::find($friend_id);       // find your friend, and...
    $friend->friends()->attach($this->id);  // add yourself, too
}
public function remove_friend($friend_id)
{
    $this->friends()->detach($friend_id);   // remove friend
    $friend = User::find($friend_id);       // find your friend, and...
    $friend->friends()->detach($this->id);  // remove yourself, too
}

thanks Taylor for the feedback and Tony of course for bringing it up (even though the post is somewhat eldery)

[fretwellian]

Hi Taylor, In the friends example when you call friends() you would only get returned friendships where you are the user_id but you would also want friendships where you are the friend_id. I can't see how I could nicely combine the two relationships friendOf() and friendTo() to make friends().

[TiagoSilvaPereira]

@fretwellian +1

[nickkuijpers]

Cool, works for me too. Do not forget the ->get() when calling it up or you get a memory exhausted and you will be debugging for 30 mins xD

[unckleg]

Hi Taylor, In the friends example when you call friends() you would only get returned friendships where you are the user_id but you would also want friendships where you are the friend_id. I can't see how I could nicely combine the two relationships friendOf() and friendTo() to make friends().

[zenichanin]

Has anyone figured this out? I really need a solution where as @fretwellian described, I could retrieve all relationships whether it is user_id or friend_id.

[landadelcarmen]

I created a new relationship just inverting the foreign keys, it seems to work fin

public function friendsOf()
{
$friends = $this->belongsToMany('User', 'friend_user', 'friend_id', 'user_id');
return $friends;
}

[RedPlatypus]

@landadelcarmen / anyone Have you figured out a way to do this without creating a second query though? I've posted about this issue on SO. https://stackoverflow.com/questions/55113114/laravel-eloquent-inner-join-on-self-referencing-table

[tomstig]

EDIT:
I think I might've come up with a proper solution: https://stackoverflow.com/a/55752491/11335307
It works oddly smoothly in my project!

---

@RedPlatypus I am also still struggling with the same problem and I am at loss to find a solution, and I wonder that there are no better solutions than those in this post or the links that you listed in your stack overflow post. Making two queries is also no good solution when you want to find out the most recent friendship...

You might've seen that some answered to your question there. I was unfortunately not able to use the class that ifxcw contributed there.

Also Jonas Staudenmeir replied to your post and asked you to provide an example. I recently thought of using JSON to break this impasse and came across his laravel-hasmany-on-json-field class: https://stackoverflow.com/questions/47222856/laravel-hasmany-on-json-field . I am in the bit luckier position than you as I don't distinguish between the two friends. I only have the columns "friend1" and "friend2", so I thought I could merge them to "friends" and add something like "{]id1],[id2]}", or maybe {"friend1": 37, "friend2": 100}. But I haven't yet had time to check whether Jonas' class can solve this.

Lastly, in this regard, I also came across https://github.com/topclaudy/compoships, but if I see it correctly it seems to go in the other direction, not allowing "column1=1 OR column2=1" the opposite, "column1=1 AND column2=1".

Also union, merge, or push don't really solve the problem (https://stackoverflow.com/questions/29751859/laravel-5-hasmany-relationship-on-two-columns), but this turns it in a collection, so no more eager loading...

So I'm still searching... It's a bit frustrating as the solution with raw sql is so easy... Anyone else with a new idea?