title: mySql force item in front of siblings in nested set
body: <p>I have hierarchical data (categories) stored in a mySql database using the nested set model.</p>
  
  <p>All is working perfectly only I now need the ability to add a property to any node (<code>force_to_top</code>) which will set the node to temporarily force to the top of its siblings, and I'm not 100% sure how to do this without physically changing the <code>lft</code> and <code>rgt</code> values.</p>
  
  <p>When a nodes <code>force_to_top</code> property is turned on I don't want to store the original <code>lft</code> and <code>rgt</code> values elsewhere, change the <code>lft</code> and <code>rgt</code> values to move the node to the correct position, and then reset their original <code>lft</code> and <code>rgt</code> values once the <code>force_to_top</code> property is removed. I think that's going to cause massive problems further down the line, plus it seems like a very roundabout way of doing things? (correct me if that's in fact the simplest way! But I would like to find another, preferably only using sql not updating any values in the db)</p>
  
  <p>I was wondering if it would be possible to somehow manipulate the output of the sql depending on the <code>force_to_top</code> property?</p>
  
  <p>For example if we have the following structure</p>
  
  <pre><code>        Cars
          |
  -----------------
  |       |       |
  Audi    Ford    VW
  </code></pre>
  
  <p>Which would have the following lft/rgt/force_to_top values:</p>
  
  <pre><code>node    lft    rgt  force_to_top
  Cars    1      8    false
  Audi    2      3    false
  Ford    4      5    false
  VW      6      7    false
  </code></pre>
  
  <p>When we output the child nodes of 'Cars', we'd be given:</p>
  
  <pre><code>Audi
  Ford
  VW
  </code></pre>
  
  <p>But lets say the node 'VW' has its <code>force_to_top</code> property set to <code>true</code>, what I would really need is the output of the child nodes of 'Cars' to now be:</p>
  
  <pre><code>VW
  Audi
  Ford
  </code></pre>
  
  <p>As I mentioned I've thought about storing the original <code>lft</code> and <code>rgt</code> values and resetting them after the <code>force_to_top</code> property is removed. The plus side of not doing it this way is the speed of turning off the <code>force_to_top</code> property, it would mean no data manipulation other than actually setting the <code>force_to_top</code> value to <code>false</code> again and the node would immediately drop back to its original position. And would also mean I don't need to create a storage area / extra tables / extra fields for the original <code>lft</code> and <code>rgt</code> values of each node.</p>
  
  <p>I've also thought about doing this in the php after I've fetched the results, but for large data sets that could really slow things down.</p>
  
  <p>I originally had to set the <code>force_to_top</code> nodes to the top of everything, which was very easy, simply <code>ORDER BY force_to_top, lft</code>, however now only forcing to the top of its siblings is a lot more complicated, as the node may be in the centre of a massive hierarchy.</p>
  
  <p>Any suggestions or a point in the right direction would be much appreciated.</p>