How to implement row reordering with dnd in dgrid

Question

I have created a grid in dgrid that is connected to JsonRest store. This loads data from pyramid backend. I have also added DnD extension to the store. The DnD works, however I don't know how to make it send any meaningful data when I drag the rows. Currently it sends two requests, one GET and one PUT, but the PUT contains just the data from rows but nothing that I could use to update the order in the database.

So what configuration is necessary in my grid, so that I can get the new ordering information?

Answer 1

This is what I do:

1. maintain a position column in the database
2. call store.put when a row is dropped, passing the id of the dropped item and the position it was dropped at.
3. When updating the position, shift the other items accordingly

Here is my onDropInternal function. It handles dropping multiple rows as well:

        onDropInternal: function(nodes, copy, targetItem) {

            var store = this.grid.store, grid = this.grid, targetRow, targetPosition;

            if (!this._targetAnchor) return

            targetRow = grid.row(this._targetAnchor);
            targetPosition = parseInt(targetRow.data[grid.orderColumn]);
            responses = 1;

            nodes.forEach(function(node, idx){
                targetPosition += idx;
                var object = {id:grid.row(node).id};
                object[grid.orderColumn] = targetPosition;
                store.put(object).then(function() {
                        if (responses == nodes.length) grid.refresh();
                        else responses++;
                });
            });

        }

Here's the PHP code I used to update the position. $fields is an associative array representing the record to store. It also assumes the existence of two functions: query and query_row, which I trust you can handle replacing if you choose to utilize this technique.

$table = "my_table";
$field = "position_field";
if (empty($fields['id'])) {
    //for new records, set the position field to the max position + 1
    $h = query_row("SELECT MAX(`$field`) as highest FROM $table LIMIT 1");
    $fields[$field] = $h['highest']+1;
} else if (is_numeric($fields[$field])) {
    //we want to move the row to $target_position
    $target_position = $fields[$field];
    //first get the original position
    $row = query_row("SELECT id,$field FROM $table WHERE id='$fields[id]' LIMIT 1");
    //start a list of ids
    $ids = $row['id'];
    //if the original position is lower than the target postion, set the incrementor to -1, otherwise 1
    $increment = ($row[$field] < $target_position) ? -1 : 1;
    //start a while loop that goes as we long as we have a row trying to take a filled position
    while (!empty($row)) {
        //set the position
        query("UPDATE $table SET $field='$target_position' where id='$row[id]'");
        //get the other row with this position (if it exists)
        $row = query_row("SELECT id,$field FROM $table WHERE id NOT IN ($ids) && `$field`='$target_position' LIMIT 1");
        //add it's id to the list of ids to exclude on the next iteration
        $ids .= ", ".$row['id'];
        //increment/decrement the target position
        $target_position += $increment;
    }
}

You could probably make this more efficient by using a query that updates multiple records at once, but the advantage of this is that it handles unexpected gaps in position numbers well.