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

jocapc

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

allan

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

jocapc

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

Oscar

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

jlavere

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

jlavere

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?

Ironwil616

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.

poetasimbolista

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.

danielsen

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.

racheld

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!

jrizzi1

@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

judodan

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?

komserbey

The plugin is not working with jQuery 1.9x :(
Could you help me?

samar

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

suneelpandya

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]

suneelpandya

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

suneelpandya

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

dhanu

How to implement rows reordering in client side?
Row reordering functionality, any helpful for adding new rows with specific index?

pserpa

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

pserpa

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

pserpa

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.

torvad

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!

panik

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

panik

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;");

zobla

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.