Drag and Drop...trying to make it happen

[eluser]NateL[/eluser]
So the jQuery UI has nifty Drag and Drop functionality and I'm trying to figure out how to implement it into CodeIgniter.

My first i'm trying to wrap my head around is how that information is stored in a database.

Lets say we have 5 names, stored in this particular order in the database:

1. John
2. Julie
3. Bill
4. Eric
5. Fred


Using Drag and drop, we re-arrange those names into the order we want - John is position 1, Julie is position 2 - when they're swapped, what would be a good method for swapping the position number for each name?

Any tips to get me going in the right direction would be much appreciated.

[eluser]jedd[/eluser]
Noice.

What schema are you using to manage sequence within your DB?

[eluser]NateL[/eluser]
[quote author="jedd" date="1259900447"]Noice.

What schema are you using to manage sequence within your DB?[/quote]

Well, not even that has been determined yet.

My first thought is ID, Name, Position

ID is, of course, auto-incrementing
Name is the person's name
Position is the order which they will display.

The tricky part I'm trying to avoid is if I swap the first two (John's position goes from "1" to "2") - how will i tell Julie's position to go from "2" to "1" ?

If that doesn't happen, then I have two items at position "2" - which will cause some ugly conflicts.

[eluser]jedd[/eluser]
[quote author="NateL" date="1259900636"]
My first thought is ID, Name, Position
[/quote]

Never done it, as sort order has always been such an ephemeral thing - something I'm happy to determine at the time I call the DB, rather than something I want each user to be able to modify back into the database.

Two methods seem feasible - the first, you've described, where you have an order field in the DB. The second is (effectively) a doubly-linked list that you maintain in the DB. Neither seems particularly elegant, and so presumably (hopefully) there are better algorithms out there.

The big problem with the position method is that if you bring the last item to the front of the list, you have to modify every row in your table. This is expensive (and a bit messy).

The big problem with the linked list is that you're kind of replicating what the database should be doing for you anyway - though as I say, I can't think of other patterns for this problem. Moving one item around means that you have, worst case, 5 rows to modify (very rough on-fingers calculation).

The tricky part I'm trying to avoid is if I swap the first two (John's position goes from "1" to "2") - how will i tell Julie's position to go from "2" to "1" ?

If that doesn't happen, then I have two items at position "2" - which will cause some ugly conflicts.

This bit seems quite easy - you just check for whatever position you're moving to, for the extant item in same (the row with that position number) and change both. Or, more accurately, change all items that will be affected. Swapping items #1 and #2 obviously means only two rows get changed.

[eluser]raitucarp[/eluser]
maybe you should use jquery plug-in called "Table Drag and Drop JQuery plugin"

try to search on uncle google :lol:

[eluser]Dyllon[/eluser]
You can try something like this, it's a quick, dirty and untested version of something I'm currently using.

Assumes your table contains the columns 'id' and 'position'

names model:

Code:

function moveName($id, $destination_id)
{

    //Do some error checking
    if ($id == $destination_id)
    {
        return FALSE;
    }

    //get destination row
    $query = $this->db->getwhere('names', array('id'=> $destination_id));
    if ($query->num_rows() == 0)
    {
        return FALSE;
    }
    else
    {
        $destination = $query->row();
    }

    //get departure row
    $query = $this->db->getwhere('names', array('id'=>$id));

    if ($query->num_rows() == 0)
    {
        return FALSE;
    }
    else
    {
        $departure = $query->row();
    }

    if ($departure->position > $destination->position)
    {
        $direction = 'up';
        $new_position = $destination->position +1;
    }
    elseif ($departure->position < $destination->position)
    {
        $direction = 'down';
        $new_position = $destination->position;
    }

    //Shift all affected names up or down
    if ( ! $this->_shiftNames( $direction, $destination->position, $departure->position ) )
    {
        return FALSE;
    }

    //Move name from departure to destination
    $this->db->where('id', $id);

    return $this->db->update('names', array('position' => $new_position));
}


function _shiftNames($direction, $start, $stop)
{
    switch ($direction)
    {
        case 'up':
        $this->db->set('position', 'position +1', FALSE);
        $this->db->where('position >', $start);
        $this->db->where('position <', $stop);
        break;

        case 'down':
        $this->db->set('position', 'position -1', FALSE);
        $this->db->where('position <=', $start);
        $this->db->where('position >', $stop);
        break;

        default:
        return FALSE;
    }

    return $this->db->update('names');
}

In your controller:

Code:

//Move a name to a new location
function ajaxMove($id = null, $destination = null)
{
    $json = array();
    if (isset($id) && isset($destination))
    {
        $json['success'] = $this->names_model->moveName($id, $destination);
    }
    else
    {
        $json['success'] = FALSE;
    }

    $this->output->set_output( json_encode($json) );
}

[eluser]NateL[/eluser]
Thanks for the replies, and thanks Dyllon for taking to the time to do all that.

I found the Table Drag and Drop JQuery plugin (I think) and it looks like the PHP that it's running will simply run a ForEach loop if the position has been updated:

Code:

<?php
$result = $_REQUEST["table-3"];
foreach($result as $value) {
    echo "$value<br/>";
}
?&gt;

Is it efficient to run that loop with an update query inside it so that it updates all of the rows? or is that the "expensive" you were referring to, Jedd?

[eluser]Dyllon[/eluser]
[quote author="NateL" date="1259977246"]
Is it efficient to run that loop with an update query inside it so that it updates all of the rows? or is that the "expensive" you were referring to, Jedd?[/quote]

In short, no it is not an efficient method.

Your table drag & drop plugin will have a callback after you "drop" a table row, use that callback with ajax to run a method in your controller like the one I posted above or your own method.