title: Closure Table (Moving Subtrees)
body: <p>Hi I have a database design of </p>
  
  <pre><code>+-------------+--------------+-------+
  | referral_id | referrent_id | level |
  +-------------+--------------+-------+
  </code></pre>
  
  <p>In my database, I have a the rows of data like this:</p>
  
  <pre><code>+---+---+---+
  | 1 | 1 | 0 |
  +---+---+---+
  | 1 | 2 | 1 |
  +---+---+---+
  | 1 | 3 | 1 |
  +---+---+---+
  | 1 | 4 | 1 |
  +---+---+---+
  | 2 | 2 | 0 |
  +---+---+---+
  | 3 | 3 | 0 |
  +---+---+---+
  | 4 | 4 | 0 |
  +---+---+---+
  </code></pre>
  
  <p>Basically the tree is </p>
  
  <pre><code>  / 2
  1 - 3
    \ 4
  </code></pre>
  
  <p>And I'm trying to move 3 to be under 2</p>
  
  <pre><code>  / 2 - 3
  1 - 4
  </code></pre>
  
  <p>My implementation: </p>
  
  <pre><code>public function changeReferrer(Member $newReferrer) {
          $oldReferrer = $this-&gt;getReferrer(1); // this retrieves the ancestor one level up
          DB::select('DELETE a FROM Referrer_Referent AS a
                      JOIN Referrer_Referent as d ON a.referent_id = d.referent_id
                      LEFT JOIN Referrer_Referent AS x
                      ON x.referrer_id = d.referrer_id AND x.referent_id = a.referrer_id
                      WHERE d.referrer_id = '.$oldReferrer-&gt;id.'
                      AND x.referrer_id IS NULL');
  
          DB::select('INSERT into Referrer_Referent (referrer_id, referent_id, level)
                      SELECT supertree.referrer_id, subtree.referent_id, supertree.level + subtree.level + 1
                      FROM Referrer_Referent as supertree JOIN Referrer_Referent as subtree
                      WHERE subtree.referrer_id = '.$oldReferrer-&gt;id.'
                      AND supertree.referent_id = '.$newReferrer-&gt;id);
      }
  </code></pre>
  
  <p>So I would call my function as <code>$user3-&gt;changeReferrer($user2);</code> But I'm getting these errors:</p>
  
  <blockquote>
    <p>Integrity constraint violation: 1062 Duplicate entry '1-1' for key 'PRIMARY' (SQL: INSERT into Referrer_Referent (referrer_id, referent_id, level) SELECT supertree.referrer_id, subtree.referent_id, supertree.level + subtree.level + 1 FROM Referrer_Referent as supertree JOIN Referrer_Referent as subtree WHERE subtree.referrer_id = 1 AND supertree.referent_id = 2)</p>
  </blockquote>
  
  <p>Attaching this <a href="https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/" rel="nofollow noreferrer">blog</a> as reference.</p>