cakephp save drag and drop list order to database

Question

I have a list and I want to let users reorder the list. For the front-end functionality, I am using UIkit. The list can be reordered on the front end up but I am having a difficult time figuring out how to save the list order to the database. In the table, there is a "sort" row. So what I am trying to do is update the sort row according to how the user orders the list.
I am trying to do this by getting the order of the items by their IDs then I am using ajax to send the data to the controller. Right now when I reorder the list the response is 200 Ok and the payload shows the project id and the ids in the order that the user set. What I do not know how to do is use that data to update the database. I was thinking I could loop through the ids and set the "sort" to the index of the loop, but I am unsure about how to do this in CakePHP.
The other thing I am sure of is if I should be using the list method that was already defined or if I should create a new method to handle this functionality.
Any help would be appreciated. This is what I have so far.
List page Jquery code:

    UIkit.util.on('.uk-sortable', 'stop', function (item) {
    let ids = ""
    $('.uk-sortable tr').each(function() {
        id = $(this).attr('id'); //this is the project id
        if(ids =='') {
            ids = id;
        }else {
            ids = ids + ', '+id ;
        }
    });
    console.log($(this).data('id'));
    $.ajax({
        url:'admin/cost/list',
        type: 'POST',
        dataType: 'json',
        data: {
            'id': $(this).data('id'),
            'ids':'ids='+ids, 
        },
        success:function() {
          UIkit.notification(`sent successfully`, 'success');
        }
        
    });

});

Controller :

public function list($project_id = null)
{
    // ----------------------------------------
    // 初期処理
    // ----------------------------------------
    $now = Chronos::now();
    
    // ----------------------------------------
    // 検索フォーム設定
    // ----------------------------------------
    $form = new ListForm();

    if (!$project_id) {
        // 施工管理トップの表示
        return $this->redirect(['prefix' => 'Admin', 'controller' => 'Build', 'action' => 'list']);
    }

    try {
        // ----------------------------------------
        // モデル設定
        // ----------------------------------------
        $this->loadModel('Projects');
        $this->loadModel('Costs');
        $this->loadModel('CostUpdates');
        $this->loadModel('Pays');
        $this->loadModel('PayDetails');
        $this->loadModel('Specs');
        
        //my code so far
        if ($this->request->is('ajax')) {
            $id = $this->request->getQuery('id');//project id
            $ids = $this->request->getQuery('ids');//items ids, ordered by user
            //this is the query to select the items I need to update
            $q_update = $this->Specs->find();
            $q_upddate->where([
                'Specs.project_id' => $project_id,
                'Specs.publish' => Configure::read('CST_APP.MST_FLAG.PUBLISH.OPENED'),
                'Specs.status' => Configure::read('CST_APP.MST_FLAG.STATUS.CLOSED'),
                'Specs.mng_kbn1' => Configure::read('CST_APP.MST_FLAG.COST_TYPE.NORMAL'),
                'Specs.price1 IS NOT' => NULL,
            ]);
            // get separate ids
            $arr = explode(',',$ids);
            //loop and update sort by the index?
            for($i=1;$i<=count($arr);$i++)
            {
               How to update the database in the loop
            }
            
        }
        // ----------------------------------------
        // プロジェクト情報取得
        // ----------------------------------------
        $query_project = $this->Projects->find();
        $query_project->contain(['Users']);
        $query_project->where([
                'Projects.project_id' => $project_id,
                'Projects.status' => Configure::read('CST_APP.MST_FLAG.STATUS_KBN.VALID'),
                'Users.status' => Configure::read('CST_APP.MST_FLAG.STATUS_KBN.VALID')
        ]);
        $project = $query_project->first();

        // ----------------------------------------
        // 施工費用データ取得
        // ----------------------------------------
        $query = $this->Costs->find();
        $query->where([
                'Costs.project_id' => $project_id
        ]);
        $cost = $query->first();
        
        // ----------------------------------------
        // お支払いデータ取得
        // ----------------------------------------
        $q_pay = $this->Pays->find();
        $q_pay->where([
            'Pays.project_id' => $project_id
        ]);
        $pay = $q_pay->first();
        
        // ----------------------------------------
        // 施工費用支払新規登録
        // ----------------------------------------
        if(!$pay){
            
            // トランザクション開始
            $connection = ConnectionManager::get('default');
            $connection->begin();
            
            // 新規データの作成
            $pay_new = $this->Pays->newEmptyEntity();
            
            // ----------------------------------------
            // 施工費用支払データの設定
            // ----------------------------------------
            $data['user_id'] = $project->user_id;
            $data['project_id'] = $project->project_id;
            //$data['cost'] = $cost->etc_cost3;
            
            $data['sort'] = 1;
            $data['status'] = Configure::read('CST_APP.MST_FLAG.STATUS_KBN.VALID');
            
            // 更新日時関連
            $data['created_at'] = $now;
            $data['created_by'] = $this->loginUserInfo['admin_id'];

            // ----------------------------------------
            // データの登録＆更新
            // ----------------------------------------
            $this->Pays->patchEntity($pay_new, $data);
            $this->Pays->saveOrFail($pay_new);
            
            // トランザクションコミット
            $connection->commit();
            
        }

        // ----------------------------------------
        // 変更費用データ取得
        // ----------------------------------------
        $q_upd = $this->CostUpdates->find();
        $q_upd->where([
                'CostUpdates.project_id' => $project_id
        ]);
        $q_upd->order(['CostUpdates.cost_update_id' => 'ASC']);

        // ----------------------------------------
        // 契約金額内訳データ取得
        // ----------------------------------------
        $q_cost_detail = $this->Specs->find();
        $q_cost_detail->where([
                'Specs.project_id' => $project_id,
                'Specs.publish' => Configure::read('CST_APP.MST_FLAG.PUBLISH.OPENED'),
                'Specs.mng_kbn1' => Configure::read('CST_APP.MST_FLAG.COST_TYPE.PLAN'),
                'Specs.price1 IS NOT' => NULL,
        ]);
        $q_cost_detail->order(['Specs.spec_id' => 'ASC']);
        // ----------------------------------------
        // 変更金額内訳データ取得
        // ----------------------------------------
        $q_upd_detail = $this->Specs->find();
        $q_upd_detail->where([
                'Specs.project_id' => $project_id,
                'Specs.publish' => Configure::read('CST_APP.MST_FLAG.PUBLISH.OPENED'),
                'Specs.status' => Configure::read('CST_APP.MST_FLAG.STATUS.CLOSED'),
                'Specs.mng_kbn1' => Configure::read('CST_APP.MST_FLAG.COST_TYPE.NORMAL'),
                'Specs.price1 IS NOT' => NULL,
        ]);
        $q_upd_detail->order(['Specs.sort' => 'DESC']);
        
        // ----------------------------------------
        // お支払い内訳データ取得
        // ----------------------------------------
        $q_pay_detail = $this->PayDetails->find();
        $q_pay_detail->where([
            'PayDetails.project_id' => $project_id,
            'PayDetails.sort IS NOT' => NULL
        ]);
        $q_pay_detail->order([
            'PayDetails.limit_date' => 'ASC'
        ]);
        $detail = $q_pay_detail->all();
        
        $final_pay = 0;
        $detail_total = 0;
        
        
        // ----------------------------------------
        // 最終お支払金額計算
        // ----------------------------------------
        if($detail){
            
            foreach($q_pay_detail as $details){
                $detail_total = $detail_total + $details->payment;
            }
        }
        
        //最終お支払い金額
        if(!empty($cost->total_cost)){
            $final_pay = $cost->total_cost - $detail_total;
        }
        
        // ----------------------------------------
        // 最終お支払金額取得
        // ----------------------------------------
        $q_detail_last = $this->PayDetails->find();
        $q_detail_last->where([
            'PayDetails.project_id' => $project_id,
            'PayDetails.sort IS ' => NULL
        ]);
        $q_detail_last->order([
            'PayDetails.limit_date' => 'ASC'
        ]);
        $detail_last = $q_detail_last->first();
        
        if($detail_last){
            
            // ----------------------------------------
            // 最終お支払い金額変動時に更新
            // ----------------------------------------
            if($detail_last->payment != $final_pay){
                
                // トランザクション開始
                $connection = ConnectionManager::get('default');
                $connection->begin();
    
                $data_p['payment'] = $final_pay;
                $data_p['updated_at'] = $now;
                $data_p['updated_by'] = $this->loginUserInfo['admin_id'];
                
                // データの登録
                // ----------------------------------------
                $this->PayDetails->patchEntity($detail_last, $data_p);
                $this->PayDetails->saveOrFail($detail_last);
                
                // トランザクションコミット
                $connection->commit();
            }
        } else {
            // ----------------------------------------
            // 最終お支払い金額登録
            // ----------------------------------------
            // トランザクション開始
            $connection = ConnectionManager::get('default');
            $connection->begin();
            
            // ----------------------------------------
            // お支払いデータ再取得
            // ----------------------------------------
            $q_pay = $this->Pays->find();
            $q_pay->where([
                'Pays.project_id' => $project_id
            ]);
            $pay = $q_pay->first();
            
            //新規データの作成
            $detail_new = $this->PayDetails->newEmptyEntity();
            
            // ----------------------------------------
            // 内訳データの設定
            // ----------------------------------------
            $data_p['pay_id'] = $pay->pay_id;
            $data_p['user_id'] = $project->user_id;
            $data_p['project_id'] = $project->project_id;
            $data_p['pay_name'] = Configure::read('CST_APP.PAY_DETAILS.PAY_NAME.FINAL_PAY');
            $data_p['payment'] = $final_pay;
            $data_p['process_kbn1'] = Configure::read('CST_APP.MST_FLAG.PROCESS_KBN.READY');
            $data_p['process_kbn2'] = Configure::read('CST_APP.MST_FLAG.PROCESS_KBN.READY');
            $data_p['process_kbn3'] = Configure::read('CST_APP.MST_FLAG.PROCESS_KBN.READY');
            $data_p['status'] = Configure::read('CST_APP.MST_FLAG.STATUS.READY');
            
            $data_p['created_at'] = $now;
            $data_p['created_by'] = $this->loginUserInfo['admin_id'];
            $data_p['updated_at'] = $now;
            $data_p['updated_by'] = $this->loginUserInfo['admin_id'];
            
            // データの登録
            // ----------------------------------------
            $this->PayDetails->patchEntity($detail_new, $data_p);
            $this->PayDetails->saveOrFail($detail_new);
            
            // トランザクションコミット
            $connection->commit();
        }
        
            // ----------------------------------------
            // お支払い内訳表示データ取得
            // ----------------------------------------
            $q_dsp_detail = $this->PayDetails->find();
            $q_dsp_detail->where([
                'PayDetails.project_id' => $project_id,
                //'PayDetails.sort IS NOT ' => NULL
            ]);
            $q_dsp_detail->order([
                'PayDetails.sort IS NULL' => 'ASC',
                'PayDetails.limit_date' => 'ASC'
            ]);
        
        // ----------------------------------------
        // 画面表示
        // ----------------------------------------
        $this->set('cost', $cost);
        $this->set('cost_upds', $q_upd);
        $this->set('detail_costs', $q_cost_detail);
        $this->set('detail_upds', $q_upd_detail);
        $this->set('pays', $q_dsp_detail);
        $this->set(compact('form', 'project' ));
        // 画面表示
        $this->render('list');

    } catch (Exception $e) {
        throw $e;
    }
}

Answer 1

I was able to solve my problem. I will post my solution, who knows it might help someone else out. Jquery:

UIkit.util.on(".uk-sortable", "stop", function (item) {
  let ids = "";
  $(".uk-sortable tr").each(function () {
    id = $(this).data("id");
if (ids == "") {
  ids = id;
} else {
  ids = ids + ", " + id;
}
   // console.log(ids);
 });
 // console.log($(this).data('id'));
 $.ajax({
url: "/admin/cost/list-update/",
type: "GET",
dataType: "json",
data: {
  ids: "" + ids,
},
timeout: 10000,
 })
.done(function (result) {
  UIkit.notification(`?????????????????`, "success");
  //location.reload(true);
})
.fail(function (XMLHttpRequest, textStatus, errorThrown) {
  console.log(textStatus);
  console.log(errorThrown);
 });
});

Method in the Controller

public function listUpdate()
{

    // ----------------------------------------
    // ????
    // ----------------------------------------
    $now = Chronos::now();
    try {
        // ----------------------------------------
        // ?????
        // ----------------------------------------
        $this->loadModel('Specs');

        if ($this->request->is('ajax')) {
            // -------------------------------------------
            // ????????????/ Get request params
            // -------------------------------------------
            $ids = $this->request->getQuery('ids');
            // -------------------------------------------
            // seperate ids/???ID
            // -------------------------------------------
            $arr = explode(',', $ids);

            for ($i = 1; $i <= count($arr); $i ++) {
                $spec = $this->Specs->find();//get the specs
                $connection = ConnectionManager::get('default');
                $connection->begin();
                //select the spec
                $spec->where([
                    'Specs.spec_id' => $arr[$i - 1],
                ]);
                //update sort column
                $sp = array(
                    'sort' => $i
                );
                
                //change query to entitiy
                $data = $spec->first();
                //update entity info
                $data['updated_at'] = $now;
                $data['updated_by'] = $this->loginUserInfo['admin_id'];
                
                $this->Specs->patchEntity($data, $sp);
                $this->Specs->saveOrFail($data);
                $connection->commit();
            }
        }
        $status = true;
        $response = $this->response;
        $response = $response->withType('application/json');
        $response = $response->withStringBody(json_encode(compact('status')));

        return $response;
    } catch (Exception $e) {
        // ??????????????
        $connection->rollback();

        throw $e;
    }
}