php - cakephp save drag and drop list order to database
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)
{
//
Answer
Answer
Answer
Answer
// ?�?????��??�
//
Answer
Answer
Answer
Answer
$now = Chronos::now();
//
Answer
Answer
Answer
Answer
// ?�??????�?��?????�??�?�?
//
Answer
Answer
Answer
Answer
$form = new ListForm();
if (!$project_id) {
// ?�??�??�???�??�?????�??�????�?
return $this->redirect(['prefix' => 'Admin', 'controller' => 'Build', 'action' => 'list']);
}
try {
//
Answer
Answer
Answer
Answer
// ?????�??�??�?�?
//
Answer
Answer
Answer
Answer
$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
}
}
//
Answer
Answer
Answer
Answer
// ??�??�?�??��?�???�??�?��??�??�
//
Answer
Answer
Answer
Answer
$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();
//
Answer
Answer
Answer
Answer
// ?�??�???�?�???�????�???�??�
//
Answer
Answer
Answer
Answer
$query = $this->Costs->find();
$query->where([
'Costs.project_id' => $project_id
]);
$cost = $query->first();
//
Answer
Answer
Answer
Answer
// ????�??��??�??�????�???�??�
//
Answer
Answer
Answer
Answer
$q_pay = $this->Pays->find();
$q_pay->where([
'Pays.project_id' => $project_id
]);
$pay = $q_pay->first();
//
Answer
Answer
Answer
Answer
// ?�??�???�?�??�??��?��?�??��???
//
Answer
Answer
Answer
Answer
if(!$pay){
// ??�??�????��?�??��??�????��?��
$connection = ConnectionManager::get('default');
$connection->begin();
// ?��?�???�????�???�????�?
$pay_new = $this->Pays->newEmptyEntity();
//
Answer
Answer
Answer
Answer
// ?�??�???�?�??�??��??�????�???�??�?�?
//
Answer
Answer
Answer
Answer
$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'];
//
Answer
Answer
Answer
Answer
// ??�????�???�?��?????�?�??��
//
Answer
Answer
Answer
Answer
$this->Pays->patchEntity($pay_new, $data);
$this->Pays->saveOrFail($pay_new);
// ??�??�????��?�??��??�????�?????????�
$connection->commit();
}
//
Answer
Answer
Answer
Answer
// ?��?�???�?�???�????�???�??�
//
Answer
Answer
Answer
Answer
$q_upd = $this->CostUpdates->find();
$q_upd->where([
'CostUpdates.project_id' => $project_id
]);
$q_upd->order(['CostUpdates.cost_update_id' => 'ASC']);
//
Answer
Answer
Answer
Answer
// ??�??�?��????��?????�????�???�??�
//
Answer
Answer
Answer
Answer
$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']);
//
Answer
Answer
Answer
Answer
// ?��?�??��????��?????�????�???�??�
//
Answer
Answer
Answer
Answer
$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']);
//
Answer
Answer
Answer
Answer
// ????�??��??�?��?????�????�???�??�
//
Answer
Answer
Answer
Answer
$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;
//
Answer
Answer
Answer
Answer
// ????��????�??��?��?????�?��
//
Answer
Answer
Answer
Answer
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;
}
//
Answer
Answer
Answer
Answer
// ????��????�??��?��?????�??�
//
Answer
Answer
Answer
Answer
$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){
//
Answer
Answer
Answer
Answer
// ????��????�??��??�?��????��?��?��??�?�??��
//
Answer
Answer
Answer
Answer
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'];
// ??�????�???�?��???
//
Answer
Answer
Answer
Answer
$this->PayDetails->patchEntity($detail_last, $data_p);
$this->PayDetails->saveOrFail($detail_last);
// ??�??�????��?�??��??�????�?????????�
$connection->commit();
}
} else {
//
Answer
Answer
Answer
Answer
// ????��????�??��??�?��????��???
//
Answer
Answer
Answer
Answer
// ??�??�????��?�??��??�????��?��
$connection = ConnectionManager::get('default');
$connection->begin();
//
Answer
Answer
Answer
Answer
// ????�??��??�??�????�??�???�??�
//
Answer
Answer
Answer
Answer
$q_pay = $this->Pays->find();
$q_pay->where([
'Pays.project_id' => $project_id
]);
$pay = $q_pay->first();
//?��?�???�????�???�????�?
$detail_new = $this->PayDetails->newEmptyEntity();
//
Answer
Answer
Answer
Answer
// ?��?????�????�???�??�?�?
//
Answer
Answer
Answer
Answer
$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'];
// ??�????�???�?��???
//
Answer
Answer
Answer
Answer
$this->PayDetails->patchEntity($detail_new, $data_p);
$this->PayDetails->saveOrFail($detail_new);
// ??�??�????��?�??��??�????�?????????�
$connection->commit();
}
//
Answer
Answer
Answer
Answer
// ????�??��??�?��???????�???�????�???�??�
//
Answer
Answer
Answer
Answer
$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'
]);
//
Answer
Answer
Answer
Answer
// ?��???????�?
//
Answer
Answer
Answer
Answer
$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;
}
}
Source