Best way of updating a drag/drop sorting list on the DB side...

I do this two ways for my custom made CMS. Ordering pages is done via left_id right_id and depth. I use jQuery-ui Sortable to do the ordering (AJAX part). I have a table and data-page-id="the ID" so it knows how to order it. Using https://github.com/gazsp/baum for database structure.

// Sortable
    $('tbody#is_sortable').sortable({
        containment: '#page-table',
        handle: '.re-order',
        tolerance: 'pointer',
        placeholder: 'ui-state-highlight',
        opacity: 0.55,
        update: function (e, ui) {
            //$('#content .update_message').show();

            // send reorder request
            var id = $(ui.item).data('page-id');
            var left_sibling_id = $(ui.item).prev().data('page-id');

            // if left_sibling_id is undefined, must be next to home page
            if (!left_sibling_id) {
                left_sibling_id = 'parent';
            }

            if (left_sibling_id) {
                $.post('/admin/pages/ajax/re-order', {
                        id: id,
                        left_sibling_id: left_sibling_id
                    },
                    function (data) {
                        //$('#content .update_message').fadeOut(400, 0);
                    }
                );
            }

        }
    });

Sorting on Baum

if ($request->left_sibling_id == 'parent') {
            $parent = ($page->isRoot() ? Page::root() : Page::find($page->parent_id));
            //return $parent;

            if (! $page->makeFirstChildOf($parent)) {
                return [
                    'error'   => true,
                    'message' => 'Could not move that page.',
                ];
            }
        } else {
            $left_page = Page::find($request->left_sibling_id);
            if (! $page->makeNextSiblingOf($left_page)) {
                return [
                    'error'   => true,
                    'message' => 'Could not move that page.',
                ];
            }
        }

I then use jQuery Sortable again just to do a small image ordering for attached images (as well as selecting them from available => selected).

$('#image_sort_source, #image_sort_dest')
        .sortable({
            connectWith: '.droppable',
            over: function (event, ui) {
                $(ui.placeholder).parent().css({'border': '2px solid #aaa'})
            },
            out: function (event, ui) {
                $(ui.placeholder).parent().css({'border': ''})
            },
            stop: function (event, ui) {
                $(ui.item).parent().css({'border': ''})
            },
        })
        .disableSelection();

    var callback = function (event, ui) {
        var ids = [];
        $('#image_sort_dest').find('li').each(function () {
            ids.push($(this).data('id'));
        })
        $('#image_sort_order').val(ids.join());
    }

    $('#image_sort_dest')
        .on("sortreceive", function (event, ui) {
            callback(event, ui);
        })
        .on("sortstop", function (event, ui) {
            callback(event, ui);
        });

I'm sure you can use something from that.

Thanks for the reply, might be a little over complicated for what i want. I use https://github.com/bevacqua/dragula to do the front end drag and drop, a simple single list up/down, but its the backend handling im looking for on advise, nice link though good to know. :-)

Ah okay, you want simple list like jQuery Sortable. I would use a pivot with an extra field called order. That way you can attach the blocks to another table (relation) then order them by the block_id and item_id.

Add the order in to a hidden input field with a flat value like value="3,6,1,8,9" which would be the order of the items you dragged around.

You can explode that by "," and then insert it into your DB.

This is my actual JS side of thing, this is all good. Also i have done the pivot table version on something similar

dragula([document.querySelector('#sortable-listings')],{
          direction: 'vertical',
          revertOnSpill: true,
      }).on('drop', function(el, container ){
          var Lists = $(container).find('.list');
          var reOrder = [];
           $.each( Lists, function( key, value ) {
                reOrder.push({'film_id':$(value).data('film-id'),'trailer_id' : $(value).data('trailer-id')});
              });
           _UpdateFetaureTrailerOdering(el, reOrder);
         });//-- end of dragular


      function _UpdateFetaureTrailerOdering(item, listing){
          $.ajax({
              url: '/adm/trailers/reorder',
              type: 'POST', 
              data: {new_order : listing},
              success: function(res, sec){
                   $(item).find('.response').addClass("success").delay(2000).queue(function(){
                         $(this).removeClass("success").dequeue();
                     });
              },
              error: function(res, sec){
                  ///....
              }
          });
        }

Then with in the php side of things i do the following:

public function reOrderFeaturedTrailer($reordering){
        $new_list = [];
        foreach ($reordering['new_order'] as $key => $value){
                $new_list[] = [
                    'order' => $key+1,
                 'film_id' =>  $value['film_id'],
                    'trailer_id' =>  $value['trailer_id']                
             ];
        }
         DB::beginTransaction();
            $this->features->truncate();
            $update = $this->features->insert($new_list);
            if($update){
                DB::commit();
                return Response::json(['success'=> (bool)true], 200);
            }
            DB::rollBack();
            return Response::json(['success'=> (bool)false], 400);
    }

Basically it clears the db then re-enters them with the new values of the pivot table. But also with other tables that simply have an order field within them I cannot simply delete the data so do the following, to just update each records order value.

...
    foreach($new_list as $item){
        $currentitem = $this->listModel->find($item['id']);
        $currentitem->order_id = $item['order_id''];
        $currentitem->save();
    }
   .....

Im just not sure if i can do the PHP side of thing better?

Okay well this is what I use to sync the pivot table (with the extra field).

if (! $request->order) {
    $pivot_data = [];
} else {
    $order = explode(',', $request->order);
    foreach ($order as $key => $ordering) {
        $pivot_data[$ordering]['order'] = $key + 1;
    }
}
$page->images()->sync($pivot_data);

From a laravel 3 project.... pretty much exactly the same

public function post_reorder(){
        $data = Input::get('links');
        
        foreach($data as $k=>$v)
        {
            $link = Link::find($v);
            $link->order_by = $k;
            $link->save();
        }
    }