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 (
id INT NOT NULL AUTO_INCREMENT ,
testimonial TEXT NOT NULL ,
credit TEXT NULL ,
url 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() {
parent::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)
{
$data=$this->dataProvider->data[$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') . '"';
}
}
else
{
$tag = '<tr';
}
if($this->rowCssClassExpression!==null)
{
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].'">';
else
echo $tag .= '>';
foreach($this->columns as $column)
$column->renderDataCell($row);
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(
'testimonial',
'credit',
'url',
),
));
Yii::app()->clientScript->registerScript('sort_order', "
var fixHelper = function(e, ui) {
ui.children().each(function() {
$(this).width($(this).width());
});
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
}
}).disableSelection();
");
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);
$command->execute();
}
}
and that should do it.
Any comments or suggestions for improving this would be very welcome.
Hope it’s of use.
Steve