Laravel Eloquent Inner Join on Self Referencing Table

Question

I'm trying to inner join a users table to itself using an eloquent model. I've looked everywhere but can't seem to find a solution to this without creating two queries which is what I am currently doing.

A users table has a many to many relationship itself through the pivot table friends

I tried and failed inner joining Users::class to itself. The best I can get at an inner join is by running two queries and seeing if there is an overlap. Thus one person has reached out to the other and vice versa.

friends   | users
----------|------
send_id   | id
receive_id| name
is_blocked|

sample data & expected result

users.id | name
---------|------
1        | foo
2        | bar
3        | baz

friends
send_id | receive_id | is_blocked
--------|------------|-----------
1       |    2       |  0
2       |    1       |  0
1       |    3       |  0
3       |    1       |  1
2       |    3       |  0

The user should have an eloquent relationship called friends. It should be what you expect comes out of requestedFriends or receivedFriends just joined.

foo->friends
returns `baz`
bar->friends
returns `foo`
baz->friends
returns empty collection

currently using

// User.php
public function requestedFriends()
{
    $left = $this->belongsToMany(User::class, 'friends','send_id','receive_id')
        ->withPivot('is_blocked')
        ->wherePivot('is_blocked','=', 0)
        ->withTimestamps();
    return $left;
}

public function receivedFriends()
{
    $right = $this->belongsToMany(User::class, 'friends','receive_id','send_id')
        ->withPivot('is_blocked')
        ->wherePivot('is_blocked','=', 0)
        ->withTimestamps();

    return $right;
}

public function friends()
{
    $reqFriends = $this->requestedFriends()->get();
    $recFriends = $this->receivedFriends()->get();
    $req = explode(",",$recFriends->implode('id', ', '));
    $intersect = $reqFriends->whereIn('id', $req);
    return $intersect;
}

Research so far

Laravel Many to many self referencing table only works one way -> old question, but still relevant

https://github.com/laravel/framework/issues/441#issuecomment-14213883 -> yep, it works… but one way.

https://laravel.com/docs/5.8/collections#method-wherein currently the only way I have found to do this in eloquent.

https://laravel.com/docs/5.7/queries#joins -> Ideally I would find a solution using an innerjoin onto itself, but no matter which way I put the id's I couldn't get a solution to work.

A solution would

A solution would inner join a self referencing table using eloquent in laravel 5.7 or 5.8, where a relationship only exists if send_id & receive_id are present on multiple rows in the friends table.

OR

Somehow let the community know that this can't be done.

Thanks in advance!

Aventine

Please provide some sample data and the expected result. – Carpic

Answer 1

I have not checked this solution in every detail yet, but I have written a "ManyToMany" Class extending the "BelongsToMany" Class shipped with laravel, which appears to work. The class basically just overrides the "get" method, duplicating the original query, "inverting" it and just performing a "union" on the original query.

<?php

namespace App\Database\Eloquent\Relations;

use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class ManyToMany extends BelongsToMany
{

    /**
     * Execute the query as a "select" statement.
     *
     * @param  array  $columns
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function get($columns = ['*'])
    {
        // duplicated from "BelongsToMany"
        $builder = $this->query->applyScopes();

        $columns = $builder->getQuery()->columns ? [] : $columns;

        // Adjustments for "Many to Many on self": do not get the resulting models here directly, but rather
        // just set the columns to select and do some adjustments to also select the "inverse" records
        $builder->addSelect(
            $this->shouldSelect($columns)
        );

        // backup order directives
        $orders = $builder->getQuery()->orders;
        $builder->getQuery()->orders = [];

        // clone the original query
        $query2 = clone($this->query);

        // determine the columns to select - same as in original query, but with inverted pivot key names
        $query2->select(
            $this->shouldSelectInverse( $columns )
        );
        // remove the inner join and build a new one, this time using the "foreign" pivot key
        $query2->getQuery()->joins = array();

        $baseTable = $this->related->getTable();
        $key = $baseTable.'.'.$this->relatedKey;
        $query2->join($this->table, $key, '=', $this->getQualifiedForeignPivotKeyName());

        // go through all where conditions and "invert" the one relevant for the inner join
        foreach( $query2->getQuery()->wheres as &$where ) {
            if(
                $where['type'] == 'Basic'
                && $where['column'] == $this->getQualifiedForeignPivotKeyName()
                && $where['operator'] == '='
                && $where['value'] == $this->parent->{$this->parentKey}
            ) {
                $where['column'] = $this->getQualifiedRelatedPivotKeyName();
                break;
            }
        }

        // add the duplicated and modified and adjusted query to the original query with union
        $builder->getQuery()->union($query2);

        // reapply orderings so that they are used for the "union" rather than just the individual queries
        foreach($orders as $ord)
            $builder->getQuery()->orderBy($ord['column'], $ord['direction']);

        // back to "normal" - get the models
        $models = $builder->getModels();
        $this->hydratePivotRelation($models);

        // If we actually found models we will also eager load any relationships that
        // have been specified as needing to be eager loaded. This will solve the
        // n + 1 query problem for the developer and also increase performance.
        if (count($models) > 0) {
            $models = $builder->eagerLoadRelations($models);
        }

        return $this->related->newCollection($models);
    }


    /**
     * Get the select columns for the relation query.
     *
     * @param  array  $columns
     * @return array
     */
    protected function shouldSelectInverse(array $columns = ['*'])
    {
        if ($columns == ['*']) {
            $columns = [$this->related->getTable().'.*'];
        }

        return array_merge($columns, $this->aliasedPivotColumnsInverse());
    }

    /**
     * Get the pivot columns for the relation.
     *
     * "pivot_" is prefixed ot each column for easy removal later.
     *
     * @return array
     */
    protected function aliasedPivotColumnsInverse()
    {
        $collection = collect( $this->pivotColumns )->map(function ($column) {
            return $this->table.'.'.$column.' as pivot_'.$column;
        });
        $collection->prepend(
            $this->table.'.'.$this->relatedPivotKey.' as pivot_'.$this->foreignPivotKey
        );
        $collection->prepend(
            $this->table.'.'.$this->foreignPivotKey.' as pivot_'.$this->relatedPivotKey
        );

        return $collection->unique()->all();
    }

}

Pointsman

Answer 2

I came across the same problem quite some time ago and have thus been following this problem closely and have made a lot of research. I have come across some of the solutions you have also found, and some more, and also have thought of other solutions that I summed here, mostly how to get both user_ids in the same column. I am afraid they will all not work well. I am also afraid that using any custom classes will stop you from using all of Laravel's handy relation features (especially eager loading). So I still thought what one could do, and, until one comes up with a hasMany-function on many columns, I think I have come up with a possible solution yesterday. I will show it first and then apply it to your project.

My project

Initial solution

In my project, one user partners with another one (= partnership) and then later will be assigned a commission. So I had the following tables:

USERS
id       | name
---------|------
1        | foo
2        | bar
17       | baz
20       | Joe
48       | Jane
51       | Jim 

PARTNERSHIPS
id  | partner1  | partner2  | confirmed | other_columns
----|-----------|-----------|-----------|---------------
1   | 1         | 2         | 1         |
9   | 17        | 20        | 1         |
23  | 48        | 51        | 1         |

As each user should always have only one active partnership, the non-active being soft-deleted, I could have helped myself by just using the hasMany function twice:

//user.php
public function partnerships()
{
    $r = $this->hasMany(Partnership::class, 'partner1');

    if(! $r->count() ){
        $r = $this->hasMany(Partnership::class, 'partner2');
    }

    return $r;
}

But if I had wanted to lookup all partnerships of a user, current and past, this of course, wouldn't have worked.

New solution

Yesterday, I came up with the solution, that is close to yours, of using a pivot table but with a little difference of using another table:

USERS
(same as above)

PARTNERSHIP_USER
user_id | partnership_id 
--------|----------------
1       | 1
2       | 1
17      | 9
20      | 9
48      | 23
51      | 23

PARTNERSHIPS
id  | confirmed | other_columns
----|-----------|---------------
1   | 1         |
9   | 1         |
23  | 1         |

// user.php
public function partnerships(){
    return $this->belongsToMany(Partnership::class);
}

public function getPartners(){
    return $this->partnerships()->with(['users' => function ($query){
        $query->where('user_id', '<>', $this->id);
    }])->get();
}

public function getCurrentPartner(){
    return $this->partnerships()->latest()->with(['users' => function ($query){
       $query->where('user_id', '<>', $this->id);
    }])->get();
}


// partnership.php
public function users(){
    return $this->belongsToMany(User::class);
}

Of course, this comes with the drawback that you always have to create and maintain two entrances in the pivot table but I think this occasional extra load for the database -- how often will this be altered anyway? -- is preferable to having two select queries on two columns every time (and from your example it seemed that you duplicated the entries in your friends table anyway).

Applied to your project

In your example the tables could be structured like this:

USERS
id       | name
---------|------
1        | foo
2        | bar
3        | baz

FRIENDSHIP_USER
user_id  | friendship_id
---------|------
1        | 1
2        | 1
3        | 2
1        | 2

FRIENDSHIPS 
id      |send_id* | receive_id* | is_blocked | [all the other nice stuff
--------|---------|-------------|------------|- you want to save]
1       | 1       |    2        |  0         |
2       | 3       |    1        |  0         |

[*send_id and receive_id are optional except 
you really want to save who did what]

Edit: My $user->partners() looks like this:

// user.php

// PARTNERSHIPS
public function partnerships(){
    // 'failed' is a custom fields in the pivot table, like the 'is_blocked' in your example
    return $this->belongsToMany(Partnership::class)
        ->withPivot('failed');
}

// PARTNERS
public function partners(){
    // this query goes forth to partnerships and then back to users.
    // The subquery excludes the id of the querying user when going back
    // (when I ask for "partners", I want only the second person to be returned)
    return $this->partnerships()
        ->with(['users' => function ($query){
                $query->where('user_id', '<>', $this->id);
        }]);
}

Goggles