Update Node order while Drag and drop

[dilip]

I have created the tree. I have also implemented the drag and drop
successfully. Now I am able to save the node but and its order but
its hard for me to find the logic of updating the order of other nodes
as we drop the new node. for example a tree node contains 5 files
which will have the order 1 to 5. If I drop a new node in 3rd
position the new node will get the rank 3 but how will i update the
nodes to maintain the hierarchy. I will have 2 nodes with rank 3. I
would be really glad if any one help me.

[DragonFlyEye]

Not sure how you are going about this, but I'm controlling order from
the database. In any event, what I'm doing is using the each() jQuery
function to go through and reorder the Nodes. The second line of the
code only addresses idiosyncrasies of the database, not the jsTree.
The logic I'm using to reset order looks like this:

/*
// orderNodes: Checks and saves the current order of Nodes within a
branch to the database.
*/
jQuery.fn.orderNodes = function(parentNode) {
var elements = [];
var ParentID = $(parentNode).attr('id') == 'navigationTree' ? 0 : $
(parentNode).attr('id');
$("#"+$(parentNode).attr('id')+" > ul > li").each(function(index,
elm) {
elements[index] = {'node_id' : $(elm).attr('id'),
'line' : index};
});
var data = {'data' : {'NavigationNode' : elements,
'ParentID' : ParentID}};
$.ajax({
url : '/admin/navigation/save',
data : data,
type : 'post',
success : function() { },
error : function() { alert("There has been an error communicating
with the server."); }
});

[dilip]

Thanks for your reply.... If it is the case consider we have 10 nodes
I will have 10 ajax request sending from my system right while I drag
and drop a node.

On Dec 30 2010, 2:37 am, DragonFlyEye <dragonflyey...@gmail.com>
wrote:

[vakata]

You do not need 10 request - take a look at the demo php code - it
will give you an idea - what you need is to shift the index of all
nodes of a given parent either up or down and then place the new node
in its place with the index.

This means a single request to the server and possibly 2 or 3 DB
queries.

Kindest regards,
Ivan

[Michael Davenport]

Hi Guys,

I am trying to save the sort order to my database but am having trouble with shifting children up and down depending on where you drop the node.

I have the bind move_node working now and also my Ajax is working but cannot get the logic behind the shifting nodes up and down right.

I have been trying to do everything in one main query using a case in the SQL.

.bind("move_node.jstree", function (e, data) {
        console.log(data);
       
        /*
        data.rslt contains:
        .o - the node being moved
        .r - the reference node in the move
        .ot - the origin tree instance
        .rt - the reference tree instance
        .p - the position to move to (may be a string - "last", "first", etc)
        .cp - the calculated position to move to (always a number)
        .np - the new parent
        .oc - the original node (if there was a copy)
        .cy - boolen indicating if the move was a copy
        .cr - same as np, but if a root node is created this is -1
        .op - the former parent
        .or - the node that was previously in the position of the moved node
        */

       
        $.ajax({
            "type": "POST",
            "dataType": "JSON",
            "url": "./ajax/controller.php",   
            "data": { "action" : "move", "id" : data.node.id, "parent" : data.parent, "pos" : data.position },
            "success": function (data) {
                console.log(data);
            }
        });


if(array_key_exists("action",$_REQUEST) && $_REQUEST['action'] == "move") {

    $get_max_sql = "SELECT max(pt_order) as maximum FROM pagetext WHERE parent_id = " . $parent . " ORDER by pt_order";
    $result = $mysqli->query($get_max_sql);
    $max = $result->fetch_array(MYSQLI_ASSOC);
   
    // Get all children within parent apart from the moved node
    $get_children_sql = "SELECT pt_id, pt_order as pos FROM pagetext WHERE parent_id = " . $parent . " AND pt_id != " . $node_id . " ORDER by pt_order";
    $result = $mysqli->query($get_children_sql);

    // An array containing the category ids as keys and the new positions as values
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

        //Decide on the order
        if($row['pos'] < $order) {
            $difference = -1;
        } elseif($row['pos'] >= $order) {
            $difference = 1;
        }

        //Lower Bounds
        if($row['pos'] + $difference < 0) {
            $display_order[$row['pt_id']] = 0;
        //Upper Bounds
        } elseif($row['pos'] + $difference >= $max['maximum']) {
             $display_order[$row['pt_id']] = $max['maximum'];
        //Normal Case
        } else {
            $display_order[$row['pt_id']] = ($row['pos'] + $difference);
        }
    }   

    $ids = implode(',', array_keys($display_order));
    $reorder_sql = "UPDATE pagetext SET pt_order = CASE pt_id ";
    foreach ($display_order as $id => $ordinal) {
        $reorder_sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
    }
    $reorder_sql .= "END WHERE pt_id IN ($ids)";
    #$result = $mysqli->query($reorder_sql);

    // Set the moved element to within the parent at the current $order;
    $move_sql = "UPDATE pagetext SET pt_order = " . $order . " WHERE parent_id = " . $parent . " AND pt_id = " . $node_id;
    #$result = $mysqli->query($move_sql);

    $debug[] = $display_order;
    $debug[] = $move_sql;   
    $debug[] = $get_children_sql;
    $debug[] = $reorder_sql;   
   
    echo json_encode($debug);
}

I would appreciate anyones help. Ivan you are a superstar and I know you'll be the boss with this. As you can see I have given it a go myself taken me all day.

But I'm not as smart as you guys.

Regards,
Mike

[Ivan Bozhanov]

I will provide 2 demos with 3.0.0-final (I hope this will be on Monday next week if no major bugs come up). First - a demo in PHP which will handle the filesystem, and second - a demo with a database (+ tree class).

[Michael Davenport]

Hi Ivan,

That would be great Ivan, excuse me as I do not know but are you on of the lead developers of jstree or just a contributor as it's open source?

Regards,
Mike

[Ivan Bozhanov]

I am the only developer of jstree :)
Cheers,
Ivan

[Michael Davenport]

Ok cool :-)

When saving the re-ordered children of a parent to the database would it be better to just send all of the children from jstree to my ajax file? Currently I am just sending the node and it's new position and then manually trying to do some manipulate of the order in the database.

I had it work when you were moving the node up but when you were to place a node in the middle of some children obviously some siblings will move up as well as others moving down.

Thanks Mike

[Ivan Bozhanov]

Sorry - I will focus on that when I prepare the demos. The tree class I have uses only the new position, but I can't go into detail now - it will be ready soon :)

[Michael Davenport]

Hi Ivan,

Finally finished my Ajax PHP to save the re-order in the database, It uses 6 database calls and will never use more but I am sure I could improve it. Also when you drag and drop a node into a new parent I am not sure if the order will be correct. But it works easily within the same parent etc.

I hope some of this helps - possibly speeds things up for you and gives you a starting point for your function.

if(array_key_exists("action",$_REQUEST) && $_REQUEST['action'] == "move") {

    //Get the previous order and parent id
    $get_prev_order = "SELECT pt_order, parent_id FROM pagetext WHERE pt_id = " . $node_id;
    $result = $mysqli->query($get_prev_order);
    $prev = $result->fetch_array(MYSQLI_ASSOC);   

    // Set the moved element to within the parent at the current $order;

    $move_sql = "UPDATE pagetext SET parent_id = " . $parent . " WHERE pt_id = " . $node_id;
    $result = $mysqli->query($move_sql);   
   
    //Get maximum order within the siblings
    $get_max_sql = "SELECT count(pt_order) as maximum FROM pagetext WHERE parent_id = " . $parent . " ORDER by pt_order";

    $result = $mysqli->query($get_max_sql);
    $max = $result->fetch_array(MYSQLI_ASSOC);   

    $max = ($max['maximum'] - 1);

   
    // Get all children within parent apart from the moved node
    $get_children_sql = "SELECT pt_id, pt_order as pos FROM pagetext WHERE parent_id = " . $parent . " AND pt_id != " . $node_id . " ORDER by pt_order";
    $result = $mysqli->query($get_children_sql);

    // An array containing the category ids as keys and the new positions as values
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

        #$debug[] = "-START-";
        #$debug[] = "ID= " . $row['pt_id'];
        #$debug[] = "POS= " . $row['pos'];
        #$debug[] = "MOVED NODE POS= " . $order;       

        $difference = $row['pos'] - $order;
        #$debug[] = "order - prev order= "  . ($order - $prev['pt_order']);
        if(($order - $prev['pt_order']) < 0)
            $direction = 1;
        else
            $direction = -1;
        //Move the existing node up or down where the new node is going to be placed
        if($prev['parent_id'] == $parent) {
            if($difference == 0) $difference = $direction;
        } else {
            //If the element has moved to a new parent then there is no previous order    
            //
            // TODO
        }

        #$debug[] = "difference= " . $difference;

        //Lower Bounds
        if($row['pos'] + $difference < 0) {
            $display_order[$row['pt_id']] = 0;

            #$debug[] = "lower bounds true";
        //Upper Bounds
        } elseif($row['pos'] + $difference >= $max) {
             $display_order[$row['pt_id']] = $max;
            #$debug[] = "upper bounds true";             

        //Normal Case
        } else {
            $display_order[$row['pt_id']] = ($row['pos'] + $difference);

            #$debug[] = "normal case";
        }
        #$debug[] = "ORDER= " . $display_order[$row['pt_id']];
        #$debug[] = "-END-";

    }   

    $ids = implode(',', array_keys($display_order));
    $reorder_sql = "UPDATE pagetext SET pt_order = CASE pt_id ";
    foreach ($display_order as $id => $ordinal) {
        $reorder_sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
    }
    $reorder_sql .= "END WHERE pt_id IN ($ids)";

    $result = $mysqli->query($reorder_sql);
   
    // Set the moved element to within the parent at the current $order;

    $move_sql = "UPDATE pagetext SET pt_order = " . $order . " WHERE pt_id = " . $node_id;
    $result = $mysqli->query($move_sql);       

    #echo json_encode($debug);

[Ivan Bozhanov]

Thank you for sharing - I will compare it with my class. I already have a PHP class that deals with both adjacency and nested set models. It is just not documented.

Best regards,
Ivan

[Michael Davenport]

Hi Ivan,

Hope you had a great weekend, hope you didn't work to hard. Any news on the demo?

Regards,
Mike

[Ivan Bozhanov]

I am working on it, the folder & files part is ready, the DB part is almost ready. Unfortunately I received a couple of bug reports which I want to resolve (but need additional info) before I release 3.0.0 final.