Row Reordering plugin with Drag'n'Drop capabilities is released

Row Reordering plugin with Drag'n'Drop capabilities is released

jocapcjocapc Posts: 45Questions: 0Answers: 0
edited February 2012 in Announcements
Dear friends,

I'm happy to announce that I have crated another add-on for the DataTables plugin - row reordering. This add-on enables you to reorder rows in the DataTable using Drag'n'Drop. Plugin is placed on http://jquery-datatables-row-reordering.googlecode.com/svn/trunk/index.html . You can find documentation on the wiki pages http://code.google.com/p/jquery-datatables-row-reordering/wiki/Index and live examples on http://jquery-datatables-row-reordering.googlecode.com/svn/trunk/index.html .

I have tested it in various cases and it works fine for me. I hope that you will find it useful too.

Best Regards,
Jovan

Replies

  • allanallan Posts: 63,564Questions: 1Answers: 10,479 Site admin
    Hi Jovan,

    Another great plug-in! Thanks for sharing it with us!

    In terms of implementation, this disabled the sorting on the table - I completely see the need for that since that would override your custom ordering. I wonder if it would be worth disabling sorting (bSortable) on all columns with the exception of your master sorting column (which presumably could be hidden if someone didn't want the index column to be visible).

    Really nice - I'm sure this will prove to be very useful!

    Allan
  • jocapcjocapc Posts: 45Questions: 0Answers: 0
    Hi Allan,

    Thanks, I'm glad that you like it. I have tried to allow sorting of index column but I had some inconsistencies in determining a new row position in asc/desc cases.
    I have assumed that this column will either be sorted in ascending order or even hidden, therefore this version put it in the fixed columns and disable any sorting. If anyone need descending sorting I will resolve this in the future version.

    Thanks,
    Jovan
  • OscarOscar Posts: 1Questions: 0Answers: 0
    Hi all,

    I´m student and i´m coding a project, your plugins, dataTables and rowReordering make much of the work, thanks a lot !!, but i want order by column like normal feature of dataTables.

    I want help to fix this, can you explain me where is the main problem/inconsistencies ?

    greetings,
    Oscar
  • jlaverejlavere Posts: 3Questions: 0Answers: 0
    Is it possible to lock down some rows so they cannot be dragged? Ideally, I'd like to have enabled (draggable) rows at the top of the table and (disabled) rows at the bottom with the option to toggle that ability dynamically.

    Thanks for any help.
    Jesse
  • jlaverejlavere Posts: 3Questions: 0Answers: 0
    One more question too...can the functions in your class be called programmatically? Like, can I call it to say switch row 2 with row 9?
  • Ironwil616Ironwil616 Posts: 50Questions: 0Answers: 0
    edited August 2012
    One suggestion I'd like to make is to put together a set of very simple, downloadable examples. Your documentation states that each row must have an ID, and that one column must be used for indexing. The page source for the live examples shows a: data-position="1" attribute for the rows (and also has the "id"), which is not addressed in the documentation. Is this necessary? The index column defaults to the first column. How is this changed, and can it also be hidden?

    I think your plugin is plain awesome - I'd just like to request some expansion on the setup docs when you have time.
  • poetasimbolistapoetasimbolista Posts: 1Questions: 0Answers: 0
    Hi,
    great plugin!
    Some comments:
    - The values get somehow crazy when they are not correlative, i.e. 1, 3, 6, 7. It'd be great to know the expected outcome - since this algorithm has to be replicated in server-side to keep order too.
    - It would also be great to be able to user-define the sorting function.
  • danielsendanielsen Posts: 2Questions: 0Answers: 0
    edited February 2013
    I like the plugin, works on client-side, but I couldn't find the server-side code of UpdateRowOrder.php... So far I use this plugin in codeigniter and tried several times. Here is how my queries look like (2 queries):

    If forward:
    UPDATE `table_name` SET `order_field` = (`order_field` + 1) WHERE `order_field` > '{$from}' AND `order_field` <= '{$to}';

    If back:
    UPDATE `table_name` SET `order_field` = (`order_field` - 1) WHERE `order_field` < '{$to}' AND `order_field` > '{$from}';

    and the row we move:
    UPDATE `table_name` SET `order_field` = '{$to}' WHERE `id` = '{$id}';

    Ignore the syntax, I use a db class in codeigniter and looks different, but I want to know the idea, where am I mistaking. It updates the row (the one we move) and it either sets the previous or next ones with the same value or even 0 in the `order_field`. Any suggestions would be appreciated.
  • racheldracheld Posts: 1Questions: 0Answers: 0
    Hello -

    I just want to say that this is a great plugin that has saved my days of my life recreating this functionality. Thank you!

    On the other hand the documentation on this plugin is terrible. I'm sorry but it really is lacking. This puts up a barrier for less experienced programmers as they cant figure out how to debug their issues. Please start a Wiki or something to build on the docs for this great plugin.

    The real reason I'm posting is to answer someones question about disabling the drag and drop on certain rows. In my situation I have a datatable with detail rows (click a button and details about that row open up under it). The default setting for row sorting made this new row sortable too which was not what I wanted.

    So to fix this I use the following JQuery to disable all sorting (on ALL rows) when I click the button to view details:

    $("tbody", "table#" + self.formIdentifier).sortable("disable");

    Then when I click on the close button for the details I re enable the sorting for the datatable:

    $("tbody", "table#" + self.formIdentifier).sortable("enable");

    And self.formIdentifier = 'form_id';

    Good luck all!
  • jrizzi1jrizzi1 Posts: 44Questions: 12Answers: 3
    edited July 2013
    @Danielsen you almost had it right (and i really appreciate you posting as I could not find UpdateRowOrder.php either, which makes this plugin really hard to implement)

    the correct way you would do this in codeigniter is

    If forward:
    UPDATE table_name SET order_field = (`order_field` + 1) WHERE order_field >= '{$to}' AND order_field < '{$from}';
    If back:
    UPDATE table_name SET order_field = (`order_field` - 1) WHERE order_field > '{$from}' AND order_field <= '{$to}';

    always after forward and back:
    UPDATE table_name SET order_field = '{$to}' WHERE id = '{$id}';

    the bare bones sql would be

    forward
    update table_name
    set order_field= order_field+1
    where order_field>= 1 --to
    and order_field< 3 --from;

    backward
    update table_name
    set order_field= order_field-1
    where order_field> 3 --from
    and order_field<= 4 --to;

    @racheld i couldnt agree with you more about how terrible the documentation is, and how the source code trunk/download does not contain the most important piece needed for server-side implementations UpdateRowORder.php

    The author did also do this in .NET, but i couldnt figure out the source
    http://www.codeproject.com/Articles/331986/Table-Row-Drag-and-Drop-in-ASP-NET-MVC-JQuery-Data

    Also, this link looks promising for coldfusion developers
    http://www.jonathonjoyce.co.uk/2011/04/08/updating-datatables-display-orders-by-drag-and-drop/

    I am writing my back end in coldfusion coldbox, if anyone needs sample just ask be glad to share
  • judodanjudodan Posts: 1Questions: 0Answers: 0
    The plugin is functional, but as stated above, examples would be helpful. One little quirk I ran into was that when using an iPad, if I had a button in a row, the button never gets activated because the row thinks it's being moved. Is there a way to exclude columns from initiating a drag/drop?
  • komserbeykomserbey Posts: 25Questions: 0Answers: 0
    The plugin is not working with jQuery 1.9x :(
    Could you help me?
  • samarsamar Posts: 1Questions: 0Answers: 0
    I am using reordering rows and then saving the new order back in the database,I want to have an array of updated row ids that will be sent to server.

    here is my table structure
  • suneelpandyasuneelpandya Posts: 3Questions: 0Answers: 0
    Hello All,
    After doing lots of experiments I have succeeded creating file for drag-drop feature. Hope this will help you.


    [code]
    $id = $_REQUEST['id'];
    $fromPosition = $_REQUEST['fromPosition'];
    $toPosition = $_REQUEST['toPosition'];

    $direction = $_REQUEST['direction'];

    if($direction === "back") {

    $aPosition = $toPosition+1;
    $sql = "
    UPDATE my_table
    SET ord = 0
    WHERE ord = $toPosition;
    ";
    mysql_query($sql);

    $sql = "
    UPDATE my_table
    SET ord = $toPosition
    WHERE id = $id;
    ";
    mysql_query($sql);

    $sql = "
    UPDATE my_table
    SET ord = ord + 1
    WHERE ($toPosition <= ord AND ord <= $fromPosition) and id != $id and ord != 0";
    mysql_query($sql);


    $sql = "
    UPDATE my_table
    SET ord = $aPosition
    WHERE ord = 0;
    ";
    mysql_query($sql);
    $sql = "";


    } // backward direction

    if($direction === "forward") {
    $aPosition = $toPosition-1;
    $sql = "
    UPDATE my_table
    SET ord = 0
    WHERE ord = $toPosition;
    ";
    mysql_query($sql);

    $sql = "
    UPDATE my_table
    SET ord = $toPosition
    WHERE id = $id;
    ";
    mysql_query($sql);

    $sql = "
    UPDATE my_table
    SET ord = ord - 1
    WHERE ($fromPosition <= ord AND ord <= $toPosition) and id != $id and ord != 0";
    mysql_query($sql);


    $sql = "
    UPDATE my_table
    SET ord = $aPosition
    WHERE ord = 0;
    ";
    mysql_query($sql);
    $sql = "";


    } // Forward Direction
    [/code]
  • suneelpandyasuneelpandya Posts: 3Questions: 0Answers: 0
    edited December 2013
    if($direction === "back") {
    $sql = "UPDATE my_table SET ord = 0 WHERE ord = $toPosition;";
    mysql_query($sql);
    $sql = "UPDATE my_table SET ord = $toPosition WHERE id = $id;";
    mysql_query($sql);
    $sql = "UPDATE my_table SET ord = ord + 1 WHERE ($toPosition <= ord AND ord <= $fromPosition) and id != $id and ord != 0";
    mysql_query($sql);
    $sql = "UPDATE my_table SET ord = $aPosition WHERE ord = 0;";
    mysql_query($sql);
    } // backward direction
  • suneelpandyasuneelpandya Posts: 3Questions: 0Answers: 0
    if($direction === "forward") {
    $aPosition = $toPosition-1;
    $sql = "UPDATE my_table SET ord = 0 WHERE ord = $toPosition;";
    mysql_query($sql);
    $sql = "UPDATE my_table SET ord = $toPosition WHERE id = $id; ";
    mysql_query($sql);
    $sql = "UPDATE my_table SET ord = ord - 1 WHERE ($fromPosition <= ord AND ord <= $toPosition) and id != $id and ord != 0";
    mysql_query($sql);
    $sql = "UPDATE my_table SET ord = $aPosition WHERE ord = 0;";
    mysql_query($sql);
    } // Forward Direction
  • dhanudhanu Posts: 12Questions: 0Answers: 0
    edited March 2014
    How to implement rows reordering in client side?
    Row reordering functionality, any helpful for adding new rows with specific index?
  • pserpapserpa Posts: 3Questions: 0Answers: 0

    suneelpandya, that's very smart however it is not 100% correct

  • pserpapserpa Posts: 3Questions: 0Answers: 0

    Got it! Pasting now!

    $id = $_REQUEST['id'];
    $fromPosition = $_REQUEST['fromPosition'][0];
    $toPosition = $_REQUEST['toPosition'];

    $direction = $_REQUEST['direction'];

    if($direction === "back") {
    $q1=mysql_query("SELECT id_produto,ordem FROM produtos WHERE id_produto=$toPosition");
    $res1=mysql_fetch_assoc($q1);

    $contador=1;
    $q2=mysql_query("SELECT id_produto,ordem FROM produtos WHERE ordem>=".$res1['ordem']." ORDER BY ordem DESC");
    while($res2=mysql_fetch_assoc($q2)){
        $sql = "UPDATE produtos SET ordem = ".abs($res1['ordem']+$contador)." WHERE id_produto = ".$res2['id_produto'];
        mysql_query($sql);
        $contador++;
    }
    
    
    $sql = "UPDATE produtos SET ordem = ".$res1['ordem']." WHERE id_produto = $id";
    mysql_query($sql);
    

    } // backward direction

    if($direction === "forward") {
    $q1=mysql_query("SELECT id_produto,ordem FROM produtos WHERE id_produto=$toPosition");
    $res1=mysql_fetch_assoc($q1);

    $contador=1;
    $q2=mysql_query("SELECT id_produto,ordem FROM produtos WHERE ordem<=".$res1['ordem']." ORDER BY ordem DESC");
    while($res2=mysql_fetch_assoc($q2)){
        $sql = "UPDATE produtos SET ordem = ".abs($res1['ordem']-$contador)." WHERE id_produto = ".$res2['id_produto'];
        mysql_query($sql);
        $contador++;
    }
    
    
    $sql = "UPDATE produtos SET ordem = ".$res1['ordem']." WHERE id_produto = $id";
    mysql_query($sql);
    

    } // Forward Direction

  • pserpapserpa Posts: 3Questions: 0Answers: 0

    Now, for some reason, the fromPosition parameter is an array, and in this case, unnecessary - i've worked around it.
    But then, toPosition parameter isn't the position or <tr> index - the real spot where you want to drag your selected item. It is the id attribute of the target, or the <tr> tag that has to move away from your draggable object.
    Last, forget the portuguese words, translate 'ordem' to 'order' and $contador to $counter or something and you will be just fine.

  • torvadtorvad Posts: 3Questions: 1Answers: 0

    Has anyone come up with a solution for sorting on column headers as well as drag & drop?

    Would be really nice if someone could help me with this!

  • panikpanik Posts: 2Questions: 0Answers: 0
    edited May 2014

    pserpa, I updated your CODE for my use!

    From:

    $fromPosition = $_REQUEST['fromPosition'][0];
    

    To:

    $fromPosition = is_array($_REQUEST['fromPosition']) ? $_REQUEST['fromPosition'][0] : $_REQUEST['fromPosition']; 
    

    Because if I have 3 houses in number, their method only returns me the first (only return '1' in '100')

    Thanks

  • panikpanik Posts: 2Questions: 0Answers: 0
    edited May 2014

    suneelpandya I optimize your CODE:

    $id           = $_REQUEST['id'];
    $fromPosition = is_array($_REQUEST['fromPosition']) ? $_REQUEST['fromPosition'][0] : $_REQUEST['fromPosition']; 
    $toPosition   = $_REQUEST['toPosition'];
    $direction    = $_REQUEST['direction'];
    $aPosition    = ($direction === "back") ? $toPosition+1 : $toPosition-1; 
    
    mysql_query("UPDATE site_slideshow SET slideshow_ordem = 0          WHERE slideshow_ordem = '".$toPosition."'");
    mysql_query("UPDATE site_slideshow SET slideshow_ordem = $toPosition WHERE slideshow_id = '".$id."'");
    
    if($direction === "back") {
        mysql_query("UPDATE site_slideshow SET slideshow_ordem = slideshow_ordem + 1 WHERE ($toPosition <= slideshow_ordem AND slideshow_ordem <= $fromPosition) and slideshow_id != $id and slideshow_ordem != 0 ORDER BY slideshow_ordem DESC;");                      
    } // backward direction
     
    if($direction === "forward") {     
        mysql_query("UPDATE site_slideshow SET slideshow_ordem = slideshow_ordem - 1 WHERE ($fromPosition <= slideshow_ordem AND slideshow_ordem <= $toPosition) and slideshow_id != $id and slideshow_ordem != 0 ORDER BY slideshow_ordem ASC;");                         
    } // Forward Direction
    
                
    mysql_query("UPDATE site_slideshow SET slideshow_ordem = $aPosition WHERE slideshow_ordem = 0;");
    
  • zoblazobla Posts: 1Questions: 0Answers: 0

    Nice work Jovan, great plugin.
    I 'm trying to implement it on my project, using server side code which is made by panik. My question is, which parameters need to pass from client side if I use above panik's code.

    Thanks for help.

This discussion has been closed.