Skip to content

Instantly share code, notes, and snippets.

@robertvrabel

robertvrabel/list.html

Last active February 27, 2022 15:46
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9 to your computer and use it in GitHub Desktop.
Download ZIP
Updating a database display order with drag and drop in SQL
Raw
gistfile1.txt
// Make the form fields sortable/draggable
$('.todos').sortable({
items: '.todo',
start: function(event, ui) {
// Create a temporary attribute on the element with the old index
$(this).attr('data-currentindex', ui.item.index());
},
update: function(event, ui) {
let user_id = $('#user_id').val();
let current_position = $(this).attr('data-currentindex');
let desired_position = ui.item.index();
// Reset the current index
$(this).removeAttr('data-currentindex');
// Post to the server to handle the changes
$.ajax({
type: "POST",
url: "/url-to-handle-database-updates/",
data: {
desired_position: desired_position,
current_position: current_position,
user_id: user_id
},
beforeSend: function() {
// Disable dragging
$('.todos').sortable('disable');
},
success: function(html) {
// Re-enable dragging
$('.todos').sortable('enable');
}
});
}
});
Raw
gistfile2.txt
// Determine if the user is moving the item up or down in the listing
$move = $desired_position > $current_position ? 'down' : 'up';
Raw
gistfile3.txt
// Set the display_order for the dragged item to be 0 so we can update this record later by display_order = 0
$query = "UPDATE todos
SET display_order = 0
WHERE display_order = :current_position
AND user_id = :user_id;
Raw
gistfile4.txt
// Move down: Update the items between the current position and the desired position, decreasing each item by 1 to make space for the new item
if ($move == 'down') {
$query = "UPDATE todos
SET display_order = (display_order - 1)
WHERE display_order > :current_position
AND display_order <= :desired_position
AND user_id = :user_id;
}
Raw
gistfile5.txt
// Move up: Update the items between the desired position and the current position, increasing each item by 1 to make space for the new item
if ($move == 'up') {
$query = "UPDATE `form_field_rel`
SET display_order = (display_order + 1)
WHERE display_order >= " . $desired_position . "
AND display_order < " . $current_position . "
AND form_id = " . $form_id;
}
Raw
gistfile6.txt
// Update the item that was dragged and set it to be the desired position now that the slot is opend up
$query = "UPDATE todos
SET display_order = :desired_position
WHERE display_order = 0
AND user_id = :user_id;
@wupero
Copy link
Copy Markdown

wupero commented Jul 31, 2019

For people who might set position/display_order as unique column in the database - one should add appropriate ordering in the update queries:

// Move up: Update the items between the desired position and the current position, increasing each item by 1 to make space for the new item
if ($move == 'up') {
    $query = "UPDATE todos
              SET display_order = (display_order + 1)
              WHERE display_order >= :desired_position
              AND display_order < :current_position
              AND user_id = :user_id
              ORDER BY display_order DESC";
}
// Move down: Update the items between the current position and the desired position, decreasing each item by 1 to make space for the new item
if ($move == 'down') {
    $query = "UPDATE todos
              SET display_order = (display_order - 1)
              WHERE display_order > :current_position
              AND display_order <= :desired_position
              AND user_id = :user_id
              ORDER BY display_order ASC";
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment