title: Laravel Eloquent Inner Join on Self Referencing Table
body: <p>I'm trying to <strong>inner join a users table to itself using an eloquent model</strong>. I've looked everywhere but can't seem to find a solution to this <strong>without creating two queries</strong> which is what I am currently doing.</p>
  <p>A <strong>users</strong> table has a <strong>many to many</strong> relationship itself through the <strong>pivot</strong> table <strong>friends</strong></p>
  <p>I tried and failed inner joining <code>Users::class</code> 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.</p>
  <pre><code>friends   | users
  ----------|------
  send_id   | id
  receive_id| name
  is_blocked|
  </code></pre>
  <h2>sample data &amp; expected result</h2>
  <pre><code>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
  </code></pre>
  <p>The user should have an eloquent relationship called friends. It should be what you expect comes out of <code>requestedFriends</code> or <code>receivedFriends</code> just joined.</p>
  <pre><code>foo-&gt;friends
  returns `baz`
  bar-&gt;friends
  returns `foo`
  baz-&gt;friends
  returns empty collection
  
  </code></pre>
  <h2>currently using</h2>
  <pre class="lang-php prettyprint-override"><code>// User.php
  public function requestedFriends()
  {
      $left = $this-&gt;belongsToMany(User::class, 'friends','send_id','receive_id')
          -&gt;withPivot('is_blocked')
          -&gt;wherePivot('is_blocked','=', 0)
          -&gt;withTimestamps();
      return $left;
  }
  
  public function receivedFriends()
  {
      $right = $this-&gt;belongsToMany(User::class, 'friends','receive_id','send_id')
          -&gt;withPivot('is_blocked')
          -&gt;wherePivot('is_blocked','=', 0)
          -&gt;withTimestamps();
  
      return $right;
  }
  
  public function friends()
  {
      $reqFriends = $this-&gt;requestedFriends()-&gt;get();
      $recFriends = $this-&gt;receivedFriends()-&gt;get();
      $req = explode(&quot;,&quot;,$recFriends-&gt;implode('id', ', '));
      $intersect = $reqFriends-&gt;whereIn('id', $req);
      return $intersect;
  }
  </code></pre>
  <h2>Research so far</h2>
  <p><a href="https://stackoverflow.com/questions/17567305/laravel-many-to-many-self-referencing-table-only-works-one-way">Laravel Many to many self referencing table only works one way</a> -&gt; old question, but still relevant</p>
  <p><a href="https://github.com/laravel/framework/issues/441#issuecomment-14213883" rel="nofollow noreferrer">https://github.com/laravel/framework/issues/441#issuecomment-14213883</a> -&gt; yep, it works… but one way.</p>
  <p><a href="https://laravel.com/docs/5.8/collections#method-wherein" rel="nofollow noreferrer">https://laravel.com/docs/5.8/collections#method-wherein</a>
  currently the only way I have found to do this in eloquent.</p>
  <p><a href="https://laravel.com/docs/5.7/queries#joins" rel="nofollow noreferrer">https://laravel.com/docs/5.7/queries#joins</a> -&gt; <strong>Ideally</strong> 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.</p>
  <h2>A solution would</h2>
  <p>A solution would inner join a self referencing table using eloquent in <strong>laravel 5.7</strong> or <strong>5.8</strong>, where a relationship only exists if <code>send_id</code> &amp; <code>receive_id</code> are present on multiple rows in the friends table.</p>
  <p><strong>OR</strong></p>
  <p>Somehow let the community know that this can't be done.</p>
  <p>Thanks in advance!</p>