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.
Watch out to the max_sp_recursion_depth variable inside the procedure.
Here is:

<pre><code class="sql">
-- Procedure BEGIN
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
-- Procedure END

To call the procedure:
set @parent = NULL;
set @left = 1;
CALL recalculateNetstedSet(@parent,@left);
</code></pre>
--------------------------------------------------------------------------------
I think the procedure got wrong for the wiki sintax. I will try again, just the procedure this time.
Sorry I should have seen the preview ;)

<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>
--------------------------------------------------------------------------------
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 _seems_ to have made things better.

app/project.rb Line 51

-- acts_as_nested_set :order => 'name', :dependent => :destroy
++ acts_as_nested_set :order => 'name'

Hope this helps,

Paul
--------------------------------------------------------------------------------
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 *Netsted* Set) :)

<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>
--------------------------------------------------------------------------------
Why not: @Project.update_all(:lft=>nil,:rgt=>nil); Project.rebuild!@ or something like that ?

Introducing stored procedure is limited to your own database system, while Redmine tries to support sqlite3, Postgresql and Mysql the same time.
--------------------------------------------------------------------------------
This is what I did to get the order back to work:

# Go to your database
# Make a backup of your "projects" table
# Set the value for "lft" and "rgt" to "0"
# Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order

In case anything goes wrong you still have the backup.
--------------------------------------------------------------------------------
Hi all,

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?

Thanks!
--------------------------------------------------------------------------------
Sven Culley wrote:
> This is what I did to get the order back to work:
>
> # Go to your database
> # Make a backup of your "projects" table
> # Set the value for "lft" and "rgt" to "0"
> # Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order
>
> In case anything goes wrong you still have the backup.

in redmine 2,

<pre>
4. Go to you console and use "ruby script/runner -e production 'Project.rebuild!'" to rebuild the project order
</pre>

the command above doesn't work. maybe :

<pre>
ruby script/rails runner -e production 'Project.rebuild!'
</pre>

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

relates,Closed,18860,Replace awesome_nested_set gem with a custom implementation of nested sets