My guess is that there's a bug in the implementation of renumber_full_tree/calc_numbers which is causing this problem. The dataset with overlapping numbers is what resulted from applying renumber_full_tree; thus something is not quite right with that method...
<br><br>I have a copy of SQL for Smarties (2nd ed), so I'll try and see if the two algorithms diverge at all and/or provide a patch if I can help it. :)<br><br>Thanks,<br><br>--<br>Matt<br><br><div><span class="gmail_quote">
On 8/14/07, <b class="gmail_sendername">Matt Rogish</b> <<a href="mailto:matt.rogish@gmail.com">matt.rogish@gmail.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
The problem is, as I see it, overlapping left and right columns. Shouldn't the lft/right be one apart, or am I wrong.<br><br>For example:<br><span style="font-family: courier new,monospace;">mysql> select id, parent_id, lft, rgt from messages where message_board_id = 1926 and parent_id is null order by lft;
</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">+--------+-----------+------+------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
| id | parent_id | lft | rgt |</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">+--------+-----------+------+------+</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">| 9472 | NULL | 1 | 2 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9473 | NULL | 2 | 37 |
</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9474 | NULL | 37 | 44 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
| 9475 | NULL | 44 | 45 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9476 | NULL | 45 | 74 | </span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">| 9477 | NULL | 74 | 75 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9478 | NULL | 75 | 114 |
</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9487 | NULL | 114 | 179 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
| 9479 | NULL | 179 | 348 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9488 | NULL | 478 | 515 | </span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">| 9482 | NULL | 515 | 520 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9483 | NULL | 520 | 525 |
</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 9484 | NULL | 525 | 526 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
| 9485 | NULL | 526 | 579 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 334881 | NULL | 579 | 580 | </span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">| 335201 | NULL | 581 | 584 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 335234 | NULL | 585 | 586 |
</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">+--------+-----------+------+------+</span><br><br>So the only ones "correct" are id = 335201 and id = 335234?
<br><br>I attached the entire dataset. What is happening is that the BETWEEN statement that better nested set is executing looks something like this:<br>m = Message.find( 9478 )<br><span style="font-family: courier new,monospace;">
</span>m.all_children.each{ stuff }<br><br>SQL is:<br>SELECT * FROM messages WHERE (message_board_id = 1926 AND (lft BETWEEN 75 AND 114)) ORDER BY lf<br><br>Since BETWEEN is inclusive, that result pulls all the children correctly but incorrectly pulls out the next root, message id = 9487.
<br><br>I'm not sure if that means renumber_entire_tree needs to be fixed, or the actual move_to_child does, but something isn't right.<div><span class="e" id="q_11465a8a8d7bb734_1"><br><br>--<br>Matt<br><br><div>
<span class="gmail_quote">On 8/14/07, <b class="gmail_sendername">
Matt Rogish</b> <<a href="mailto:rogishmn@muohio.edu" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">rogishmn@muohio.edu</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I'm not writing any SQL, I'm using built-in better-nested-set functions.<br><br>Here's my save:<br> def create<br> @message = Message.new(params[:message])<br><br> if @message.save<br> flash[:notice] = 'Message was successfully created.'
<br> <br> if params[:message][:parent_id] <br> @message.move_to_child_of( Message.find params[:message][:parent_id] )<br> @message.save<br> end<br> end<br> end<br><br>--<br><span>
Matt</span><div><span><br>
<br>
<div><span class="gmail_quote">On 8/14/07, <b class="gmail_sendername">Jeremy Nicoll</b> <<a href="mailto:jnicoll@goldnoteexpress.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">jnicoll@goldnoteexpress.com
</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000">
I think it would be best if you could post your SQL that is messed
up as well as the code you are using to save your nodes. Unfortunately
we can't tell you what is wrong until we actually see the logic behind
what you are doing.<br>
<br>
--<br>
Jeremy Nicoll<br>
<br>
Matt Rogish wrote:
<blockquote type="cite"><div><span>How can I repair my tree?<br>
<br>
I have a message board system in which the messages are grouped by
"message_board_id", thus my model is:<br>
acts_as_nested_set :scope => :message_board_id <br>
<br>
Somehow the tree is messed up, inasmuch as some roots (parent_id ==
null) are being set to the children of some other message, so I cannot
successfully repair it. I'm not sure if I'm performing incorrect
operations or what, but somehow I need to repair my tree where
message_board_id = 34 (or whatever it is).
<br>
<br>
It seems as if renumber_full_tree tries to find the virtual root for a
given node, but since the root itself is a CHILD of another node, the
whole thing fails. My layman's guess is that the "root" condition is
wrong, since it's doing WHERE parent_id IS NULL ... AND lft BETWEEN ..
AND ..
<br>
<br>
Well, if the root is incorrectly between something, then the lft would
fail, no? Maybe I'm misreading that. Help!!<br>
<br>
Thanks,<br>
<br>
--<br>
Matt<br>
<br>
<div><span class="gmail_quote">On 8/14/07, <b class="gmail_sendername">
Matt Rogish</b> <<a href="mailto:rogishmn@muohio.edu" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">rogishmn@muohio.edu</a>> wrote:</span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Under
heavy load we are getting significantly screwed up data. I have to run
renumber_full_tree in script/console production order to repair the
thing.<br>
<br>
Obviously this is a bad thing, but I don't even know where to start to
fix it. Any ideas?
<br>
<br>
Thanks,<br>
<br>
--<br>
<span>Matt<br>
</span></blockquote>
</div>
<br>
</span></div><pre><hr size="4" width="90%">
_______________________________________________<br>Betternestedset-talk mailing list<br><a href="mailto:Betternestedset-talk@rubyforge.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">Betternestedset-talk@rubyforge.org
<br><br><br></a>
<a href="http://rubyforge.org/mailman/listinfo/betternestedset-talk" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://rubyforge.org/mailman/listinfo/betternestedset-talk</a>
</pre>
</blockquote>
</div>
<br>_______________________________________________<br>Betternestedset-talk mailing list<br><a href="mailto:Betternestedset-talk@rubyforge.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">Betternestedset-talk@rubyforge.org
</a><br><a href="http://rubyforge.org/mailman/listinfo/betternestedset-talk" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://rubyforge.org/mailman/listinfo/betternestedset-talk</a><br><br></blockquote>
</div><br>
</span></div></blockquote></div><br>
</span></div><br clear="all"></blockquote></div><br>