Drag & Drop Ordering in a CGridView Widget

Hello all

I’m posting in the tips section as this is working and someone may find it useful but I am really hoping for feedback on making it more efficient and secure as I’ve only been playing around with Yii for a couple of weeks and my PHP knowledge is well out of date so I’m not entirely confident in it. If I can get this refined with feedback on the forums I might write up a Wiki as they’ve been really useful to me.

Gii was one of my main reasons for choosing to work with Yii and the DataGrid widget is particularly useful but there are a few functions I’m trying to add in. One thing I use a lot in my database designs is a sort_order column for retrieving records in an order I can manipulate so I’ve added the function to the DataGrid to drag and drop table rows and have the sort_order column updated in the database.

I would use the usual blog example but they’re always ordered by date so: A database table holds testimonials for display in a given order on the public website:

[sql]CREATE TABLE IF NOT EXISTS testimonial (


testimonial TEXT NOT NULL ,

credit TEXT NULL ,


sort_order INT NOT NULL ,

PRIMARY KEY (id));[/sql]

Step 1: In the DataGrid I set the <tr> tags to hold the attributes data-record-id and data-sort-order (HTML 5 data attributes are great!) with the values from the database. To do this I extended CGridView and override the renderTableRow method, so in protected/components/AltDataGrid.php:

class AltGridView extends CGridView {

	public function init() {




	 * Renders a table body row.

	 * @param integer $row the row number (zero-based).

	 * Adds in data for drag & drop ordering if id and sort_order fields available.


	public function renderTableRow($row)



		if (array_key_exists('id', $data->tableSchema->columns)) { 

			$tag = '<tr data-record-id="'. CHtml::value($data,'id') . '"';

			if(array_key_exists('sort_order', $data->tableSchema->columns)) {

				$tag .= ' data-sort-order="'. CHtml::value($data,'sort_order') . '"';





			$tag = '<tr';




			echo $tag .= ' class="'.$this->evaluateExpression($this->rowCssClassExpression,array('row'=>$row,'data'=>$data)).'">';


		else if(is_array($this->rowCssClass) && ($n=count($this->rowCssClass))>0)

			echo $tag .= ' class="'.$this->rowCssClass[$row%$n].'">';


			echo $tag .= '>';

		foreach($this->columns as $column)


		echo "</tr>\n";



Step 2: In the view I redirected the call from CGridView to the new AltGridView and registered the drag & ajax on drop javascript (created with a little help from: (seems I can’t post links but it’s nickadeemus2002 on jsfiddle)). So now in /protected/views/testimonial/admin.php:

$this->widget('AltGridView', array(

	'id' => 'testimonial-grid',

	'dataProvider' => $model->search(),

	'enableSorting' => false, // Drag & Drop ordering won't work if the column sorting is used!

	'filter' => $model, // Drag & Drop ordering on filtered grid will still work

	'columns' => array(






Yii::app()->clientScript->registerScript('sort_order', "

	var fixHelper = function(e, ui) {

	    ui.children().each(function() {



		return ui;

	}; // Stops the table row being dragged from collapsing

	$('#testimonial-grid tbody').sortable({

		helper: fixHelper,

		update: function(event, ui) { 

			var data = {'ids' : [], 'sort_orders' : []}; // Setup the post array

			$(this).children('tr').each(function() {

				data['ids'].push($(this).attr('data-record-id')); // Add the id values to the post array in order


			$(this).children('tr').each(function() {

				data['sort_orders'].push($(this).attr('data-sort-order')); // Add the sort_order values to the post array in order


			data['sort_orders'].sort(); // Sort the sort_order values to represent the new order

			$.post('" . $this->createUrl('reorder') . "', data); // Post to TestimonialController.php actionReOrder									




Step 3: Added to the controller is a new action to handle reordering, firstly it seems highly inefficient to load and update what could be dozens of model instances so I’ve not done it that way, I think the best way to update multiple records with different values in one query is using a CASE statement, I couldn’t find any information on doing this using active record and I’m not sure on parameter binding with the query being generated in a foreach loop so I’ve just done a simple intval to protect against SQL injection, better suggestions anyone?. Also I suppose this is better in the Model or in a class of it’s own for use on other tables so again, suggestions anyone? Anyway in protected/controllers/TestimonialController.php:

public function actionReOrder()


		if(isset($_POST['ids']) && isset($_POST['sort_orders'])) // Check the data is passed


			$cleanids = array(); // Needed to restrict query later

			$query = 'UPDATE testimonial SET sort_order = CASE id ';

			foreach ($_POST['ids'] as $key => $value) {

				$idvalue = intval($value); // Clean the ID value up a bit just in case

				$cleanids[] = $idvalue; // Save for later

				$ordervalue = intval($_POST['sort_orders'][$key]); // Get corresponding sort_order value & clean it up a bit

				$query .= 'WHEN ' . $idvalue . ' THEN ' . $ordervalue . ' '; // Add the CASE to the query


			$query .= 'END WHERE id IN (' . implode(',', $cleanids) . ')'; // End CASE and resrict to affected rows only

			$command = Yii::app()->db->createCommand($query);




and that should do it.

Any comments or suggestions for improving this would be very welcome.

Hope it’s of use.



I prefer this solution (to achieve same result):

Creating a jQueryUI Sortable CGridView

Easy setup & straight forward solution by far less (complicated) code than the suggested solution above (in my opinion)

hello…i want to ask about dropdown list…can anybody teach me?


why i put this coding in file C:\xampp\htdocs\e-frp\protected\views\main\portfolio.php it display error?


<div class="input-group">

<?= $form->dropdownList($User, ‘dept_id’, Department::model()->options(), array(‘class’ => ‘form-control’)); ?>

<div class="input-group-addon"><i class="fa fa-group"></i></div>



what should i write in file C:\xampp\htdocs\e-frp\protected\controllers\MainController.php at part portfolio?

public function actionPortfolio() {

if (isset($_POST[‘submit’])) {

//create new portfolio data row

$Department = Department::model()->findAll();

$CourseModel = new Course();

$CourseModel->module = Yii::app()->request->getPost("programme");

$CourseModel->code = Yii::app()->request->getPost("code");

$CourseModel->name = Yii::app()->request->getPost("coursename");

$CourseModel->user_id = Yii::app()->user->profile->user_id;




$this->render(‘portfolio’, compact(“title”, “CourseModels”));


Gii was one of my main reasons for choosing to work with Yii and the DataGrid widget is particularly useful but there are a few functions I’m trying to add in.