https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292009-08-13T12:46:49ZRedmineRedmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=105342009-08-13T12:46:49ZAugusto Dias Pereira dos Santos
<ul></ul><p>For those who had the nested set disordered in MySQL DataBase, I develop a procedure that recalculate the nested set base on the parent_id. It's not a solution, just a way to rearange the mess. It can be better.<br />Watch out to the max_sp_recursion_depth variable inside the procedure. <br />Here is:</p>
<pre><code class="sql syntaxhl"><span class="c1">-- Procedure BEGIN</span>
<span class="k">DROP</span> <span class="k">PROCEDURE</span> <span class="nv">`recalculateNetstedSet`</span><span class="o">//</span>
<span class="k">CREATE</span> <span class="k">PROCEDURE</span> <span class="nv">`recalculateNetstedSet`</span><span class="p">(</span><span class="n">parent</span> <span class="nb">INT</span><span class="p">,</span> <span class="k">INOUT</span> <span class="n">left_value</span> <span class="nb">INT</span><span class="p">)</span>
<span class="k">BEGIN</span>
<span class="k">DECLARE</span> <span class="n">done</span> <span class="nb">INT</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">;</span>
<span class="k">DECLARE</span> <span class="n">son_ID</span> <span class="nb">INTEGER</span><span class="p">;</span>
<span class="k">DECLARE</span> <span class="n">sons</span> <span class="k">CURSOR</span> <span class="k">FOR</span> <span class="k">SELECT</span> <span class="n">id</span> <span class="k">FROM</span> <span class="n">projects</span> <span class="k">WHERE</span> <span class="n">parent_id</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">;</span>
<span class="k">DECLARE</span> <span class="n">sons2</span> <span class="k">CURSOR</span> <span class="k">FOR</span> <span class="k">SELECT</span> <span class="n">id</span> <span class="k">FROM</span> <span class="n">projects</span> <span class="k">WHERE</span> <span class="n">parent_id</span> <span class="o">=</span> <span class="n">parent</span><span class="p">;</span>
<span class="k">DECLARE</span> <span class="k">CONTINUE</span> <span class="k">HANDLER</span> <span class="k">FOR</span> <span class="k">NOT</span> <span class="k">FOUND</span> <span class="k">SET</span> <span class="n">done</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="k">set</span> <span class="n">max_sp_recursion_depth</span><span class="o">=</span><span class="mi">10</span><span class="p">;</span>
<span class="n">IF</span> <span class="n">parent</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">THEN</span>
<span class="k">UPDATE</span> <span class="n">projects</span> <span class="k">set</span> <span class="n">lft</span> <span class="o">=</span> <span class="n">left_value</span> <span class="k">where</span> <span class="n">id</span> <span class="o">=</span> <span class="n">parent</span><span class="p">;</span>
<span class="k">SET</span> <span class="n">left_value</span> <span class="o">=</span> <span class="n">left_value</span><span class="o">+</span><span class="mi">1</span><span class="p">;</span>
<span class="k">END</span> <span class="n">IF</span><span class="p">;</span>
<span class="k">OPEN</span> <span class="n">sons</span><span class="p">;</span>
<span class="k">OPEN</span> <span class="n">sons2</span><span class="p">;</span>
<span class="n">IF</span> <span class="n">parent</span> <span class="k">IS</span> <span class="k">NULL</span> <span class="k">THEN</span>
<span class="k">FETCH</span> <span class="n">sons</span> <span class="k">INTO</span> <span class="n">son_ID</span><span class="p">;</span>
<span class="n">REPEAT</span>
<span class="n">IF</span> <span class="n">son_ID</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">THEN</span>
<span class="k">CALL</span> <span class="n">recalculateNetstedSet</span><span class="p">(</span><span class="n">son_ID</span><span class="p">,</span><span class="n">left_value</span><span class="p">);</span>
<span class="k">SET</span> <span class="n">left_value</span> <span class="o">=</span> <span class="n">left_value</span><span class="o">+</span><span class="mi">1</span><span class="p">;</span>
<span class="k">END</span> <span class="n">IF</span><span class="p">;</span>
<span class="k">FETCH</span> <span class="n">sons</span> <span class="k">INTO</span> <span class="n">son_ID</span><span class="p">;</span>
<span class="k">UNTIL</span> <span class="n">done</span> <span class="k">END</span> <span class="n">REPEAT</span><span class="p">;</span>
<span class="k">ELSE</span>
<span class="k">FETCH</span> <span class="n">sons2</span> <span class="k">INTO</span> <span class="n">son_ID</span><span class="p">;</span>
<span class="n">REPEAT</span>
<span class="n">IF</span> <span class="n">son_ID</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">THEN</span>
<span class="k">CALL</span> <span class="n">recalculateNetstedSet</span><span class="p">(</span><span class="n">son_ID</span><span class="p">,</span><span class="n">left_value</span><span class="p">);</span>
<span class="k">SET</span> <span class="n">left_value</span> <span class="o">=</span> <span class="n">left_value</span><span class="o">+</span><span class="mi">1</span><span class="p">;</span>
<span class="k">END</span> <span class="n">IF</span><span class="p">;</span>
<span class="k">FETCH</span> <span class="n">sons2</span> <span class="k">INTO</span> <span class="n">son_ID</span><span class="p">;</span>
<span class="k">UNTIL</span> <span class="n">done</span> <span class="k">END</span> <span class="n">REPEAT</span><span class="p">;</span>
<span class="k">END</span> <span class="n">IF</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">projects</span> <span class="k">set</span> <span class="n">rgt</span> <span class="o">=</span> <span class="n">left_value</span> <span class="k">where</span> <span class="n">id</span> <span class="o">=</span> <span class="n">parent</span><span class="p">;</span>
<span class="k">CLOSE</span> <span class="n">sons</span><span class="p">;</span>
<span class="k">CLOSE</span> <span class="n">sons2</span><span class="p">;</span>
<span class="k">END</span>
<span class="c1">-- Procedure END</span>
<span class="k">To</span> <span class="k">call</span> <span class="n">the</span> <span class="k">procedure</span><span class="p">:</span>
<span class="k">set</span> <span class="o">@</span><span class="n">parent</span> <span class="o">=</span> <span class="k">NULL</span><span class="p">;</span>
<span class="k">set</span> <span class="o">@</span><span class="k">left</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="k">CALL</span> <span class="n">recalculateNetstedSet</span><span class="p">(</span><span class="o">@</span><span class="n">parent</span><span class="p">,</span><span class="o">@</span><span class="k">left</span><span class="p">);</span>
</code></pre> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=105352009-08-13T12:53:01ZAugusto Dias Pereira dos Santos
<ul></ul><p>I think the procedure got wrong for the wiki sintax. I will try again, just the procedure this time.<br />Sorry I should have seen the preview ;)</p>
<pre>
DROP PROCEDURE `recalculateNetstedSet`//
CREATE PROCEDURE `recalculateNetstedSet`(parent INT, INOUT left_value INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE son_ID INTEGER;
DECLARE sons CURSOR FOR SELECT id FROM projects WHERE parent_id IS NULL;
DECLARE sons2 CURSOR FOR SELECT id FROM projects WHERE parent_id = parent;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set max_sp_recursion_depth=10;
IF parent IS NOT NULL THEN
UPDATE projects set lft = left_value where id = parent;
SET left_value = left_value+1;
END IF;
OPEN sons;
OPEN sons2;
IF parent IS NULL THEN
FETCH sons INTO son_ID;
REPEAT
IF son_ID IS NOT NULL THEN
CALL recalculateNetstedSet(son_ID,left_value);
SET left_value = left_value+1;
END IF;
FETCH sons INTO son_ID;
UNTIL done END REPEAT;
ELSE
FETCH sons2 INTO son_ID;
REPEAT
IF son_ID IS NOT NULL THEN
CALL recalculateNetstedSet(son_ID,left_value);
SET left_value = left_value+1;
END IF;
FETCH sons2 INTO son_ID;
UNTIL done END REPEAT;
END IF;
UPDATE projects set rgt = left_value where id = parent;
CLOSE sons;
CLOSE sons2;
END
</pre> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=110792009-09-21T14:53:53ZPaul Groves
<ul></ul><p>I too experienced this, I removed the "dependent_destroy" declaration from app/project.rb as I believe this is not applicable for the awesome_nested_set gem. This <em>seems</em> to have made things better.</p>
<p>app/project.rb Line 51</p>
<pre><code>-- acts_as_nested_set :order => 'name', :dependent => :destroy<br />++ acts_as_nested_set :order => 'name'</code></pre>
<p>Hope this helps,</p>
<p>Paul</p> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=135682010-01-14T10:43:01ZChris Platts
<ul></ul><p>Here's another version of the stored proc which tries to keep a sane alphabetical sort order on projects and subprojects. It also corrects the typo (recalculate <strong>Netsted</strong> Set) :)</p>
<pre>
CREATE DEFINER=`redmine`@`` PROCEDURE `recalculateNestedSet`(parent INT, INOUT left_value INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE son_ID INTEGER;
DECLARE sons CURSOR FOR SELECT id FROM projects WHERE parent_id IS NULL ORDER BY name;
DECLARE sons2 CURSOR FOR SELECT id FROM projects WHERE parent_id = parent ORDER BY name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set max_sp_recursion_depth=10;
IF parent IS NOT NULL THEN
UPDATE projects set lft = left_value where id = parent;
SET left_value = left_value+1;
END IF;
OPEN sons;
OPEN sons2;
IF parent IS NULL THEN
FETCH sons INTO son_ID;
REPEAT
IF son_ID IS NOT NULL THEN
CALL recalculateNestedSet(son_ID,left_value);
SET left_value = left_value+1;
END IF;
FETCH sons INTO son_ID;
UNTIL done END REPEAT;
ELSE
FETCH sons2 INTO son_ID;
REPEAT
IF son_ID IS NOT NULL THEN
CALL recalculateNestedSet(son_ID,left_value);
SET left_value = left_value+1;
END IF;
FETCH sons2 INTO son_ID;
UNTIL done END REPEAT;
END IF;
UPDATE projects set rgt = left_value where id = parent;
CLOSE sons;
CLOSE sons2;
END
</pre> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=182212010-07-11T17:57:13ZJean-Baptiste Barth
<ul></ul><p>Why not: <code>Project.update_all(:lft=>nil,:rgt=>nil); Project.rebuild!</code> or something like that ?</p>
<p>Introducing stored procedure is limited to your own database system, while Redmine tries to support sqlite3, Postgresql and Mysql the same time.</p> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=296922011-06-08T18:01:04ZSven Culley
<ul></ul><p>This is what I did to get the order back to work:</p>
<ol>
<li>Go to your database</li>
<li>Make a backup of your "projects" table</li>
<li>Set the value for "lft" and "rgt" to "0" </li>
<li>Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order</li>
</ol>
<p>In case anything goes wrong you still have the backup.</p> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=321992011-09-13T09:40:53Zprovetza provetza
<ul></ul><p>Hi all,</p>
<p>I did what Sven suggests (zero lft and rgt on projects and then Project.rebuild). Nested projects appear alphabetically inside toplevel projects, but toplevel projects are out of order. How can I make the toplevel projects appear in order?</p>
<p>Thanks!</p> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=383742012-05-23T04:09:08ZToshiya TSURU
<ul></ul><p>Sven Culley wrote:</p>
<blockquote>
<p>This is what I did to get the order back to work:</p>
<ol>
<li>Go to your database</li>
<li>Make a backup of your "projects" table</li>
<li>Set the value for "lft" and "rgt" to "0" </li>
<li>Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order</li>
</ol>
<p>In case anything goes wrong you still have the backup.</p>
</blockquote>
<p>in redmine 2,</p>
<pre>
4. Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order
</pre>
<p>the command above doesn't work. maybe :</p>
<pre>
ruby script/rails runner -e production 'Project.rebuild!'
</pre> Redmine - Defect #3722: Nested projects can get in disorderhttps://www.redmine.org/issues/3722?journal_id=610072015-01-21T04:43:19ZToshi MARUYAMA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/18860">Feature #18860</a>: Replace awesome_nested_set gem with a custom implementation of nested sets</i> added</li></ul>