Implement jQuery Sortable in Yii2

We are going to reorder the rows of the HTML table to using jQuery sortable. and save the new order to the database.

Prerequisite:

1. A database table to work with. This table must have a column named display_order.
2. An HTML table containing the data rows whose order needs to be updated.

You need to bind the table rows ordered_by display_order column. You may set the default value for display_order to be the primary key of the table. The controller method for binding the HTML table would look like this:

public finction actionIndex(){
   $model = Users::find()->orderBy(['display_order'=>SORT_ASC])->all();
   return $this->render('index', ['model'=>$model]);
}

Now let’s work on the View.

Add an id attribute to the table

<table class="table table-bordered table-striped" id="table_faq">

Add a class to the tbody so that we can iterate over its rows.

<tbody class="faqs_table">

Each table row will have the following attributes. class = faq, data-index is the primary key of the table and data-position is the current position of the row from the column display_order.

<tr class="faq" data-index="<?=$val['id'];?>" data-position="<?=$val['display_order'];?>">

Add the jQuery UI library at the end of the view and write the following JS code.

<?php
$this->registerJsFile("//code.jquery.com/ui/1.12.1/jquery-ui.min.js",['depends' => [\yii\web\JqueryAsset::className()]]);

$this->registerJS(
'$(document).ready(function(){
  $(".faqs_table").sortable({
    update:function(event, ui){
      $(this).children().each(function(index){
          if($(this).attr("data-position")!=(index+1)){
            $(this).attr("data-position",(index+1)).addClass("orderUpdated");
          }
        });

        saveNewPositions();
    }
    });

    function saveNewPositions(){
      var positions = [];
      $(".orderUpdated").each(function(){
        positions.push([$(this).attr("data-index"),$(this).attr("data-position")]);
        $(this).removeClass("orderUpdated");
        });
        
        $.ajax({
          url:"update-order",
          method:"POST",
          dataType:"text",
          data:{
            update:1,
            positions:positions
          },success:function(response){
            console.log(response);
          }
      });
    }

    
});'
);
 ?>

This code will be executed on the update event. i.e. when the position of any row is updated. We will use saveNewPositions() function to fire an AJAX request to the controller method update-order which handles saving the updated positions to the database.

The controller method is as follows:

public function actionUpdateOrder(){
if(isset($_POST)){
            foreach ($_POST['positions'] as $key => $position) {
                $index = $position[0];
                $newposition = $position[1];
                $query = Yii::$app->db->createCommand("update faqs set display_order = $newposition where id = $index")->execute();
            }
            exit('success');
        }
}

Success! Now you can drag and drop the table rows to reorder them and save their updated positions to the database on the fly.