CakePHP 2.0 AJAX sortable list that updates to databases

It is easy to create an AJAX sortable list with CakePHP 2.0, using jQuery that automatically updates the database, however it is hard to find documentation explaining how to do it.

Here is my explanation – it assumes a basic knowledge of CakePHP.

Step 1: Data

Lets use a simple categories table which includes a column for sort_order. This will be used to define the order the records are displayed on the page.

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `sort_order` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Step 2: CakePHP

Bake a model, controller and views for this table so that we have some files to work with.
You should be able to go onto your website at http://yoururl/categories and see the default index view.

Layout

In order to use the sortable feature, you will need to edit the default layout. If you don’t already have your own layout you can copy the CakePHP default one (which is used if your own one isn’t found) from /lib/Cake/View/Layouts/default.ctp and place it in app/View/Layouts/default.ctp.
Edit this so that the writeBuffer method is one of the final lines, just before the close body tag:

Js->writeBuffer();?>

This will create the HTML and JavaScript generated by the Js helper in the view.

Controller

At the top of the CategoriesController make the Js helper available to the controller.

public $helpers = array('Js');

I am going to add 2 actions, one to display the sorted categories and one to handle the AJAX requests to update the database.
The first action is called sort, all this does is retrieve all the categories sorted by the sort_order column and send them off to the view.

public function sort() {
	$this->set('categories', $this->Category->find('all',array(
		'order' => 'sort_order ASC',
		)
	));
}

The second action is called reorder.
This will receive the AJAX post and update the categories table. I’ve commented out a line that can be used for debugging. If uncommented this will write to app/tmp/logs/error.log and is a really good way to see what is happening when you are dealing with AJAX requests.

public function reorder() {
	foreach ($this->data['Category'] as $key => $value) {
		$this->Category->id = $value;
		$this->Category->saveField("sort_order",$key + 1);
	}
	//$this->log(print_r($this->data,true));
	exit();
}

The code here is just looping through the posted data which will look like this:

Array
(
    [Category] => Array
        (
            [0] => 4
            [1] => 5
            [2] => 3
            [3] => 6
            [4] => 2
            [5] => 1
        )
)

The index is the order of elements after they have been sorted and the value is the id of each element. When saving the data I am adding 1 to the index as the one coming from jQuery is zero based.

View

For the sake of simplicity I’ve included the links to the jQuery and jQuery UI libraries here but in practice you would probably want to put them in the head of your layout.
In the view I have a simple unordered list tag containing the data.
The id of each element is important as this is used by jQuery UI to format the data sent back to the server.

	
	
'>
	

Js->get('#my-list');
$this->Js->sortable(array(
	'complete' => '$.post("/categories/reorder", $("#my-list").sortable("serialize"))',
	));
?>

And that is all there is to it.
You can simply drag and drop any item in the list and behind the scenes the database will be updated with the new sort order.