How to Make Sortable Lists

Read The Full Tutorial.

Overview

Many web applications need to offer an interface to order items - think about categories in a weblog, articles in a CMS, wishes in an e-commerce website... The old fashion way of doing it is to offer arrows to move one item up or down in the list. The AJAX way of doing it is to allow direct drag-and-drop ordering with server support. This chapter will describe both ways, together with a few tips on the way to enhance your object model and to do complex queries with Creole.

What you need

Data structure

For this article, the example used will be an undefined Item table - name it according to your needs. In order to be sortable, records need at least a rank field - no need for a heap here since the sorting will be done by the user, not by the computer. So the data structure (to be written in the schema.yml) is simply:

propel:
test_item:
_attributes: { phpName: Item }
id:
name:        varchar(255)
rank:        { type: integer, required: true }

Make sure you build your model once the data structure is defined by typing in a command line interface:

$ symfony propel-build-model

You will also need a database with the same structure. The fastest way of doing it is to call:

$ symfony propel-build-sql
$ symfony propel-insert-sql

Extending the model

Before thinking about the user interactions, make sure you have a way to retrieve items by rank, to get the list of items ordered by rank, and to get the current maximum rank, by adding the following methods to the lib/model/ItemPeer.php:

static function retrieveByRank($rank = 1)
{
$c = new Criteria;
$c->add(self::RANK, $rank);
return self::doSelectOne($c);
}
 
static function getAllByRank()
{
$c = new Criteria;
$c->addAscendingOrderByColumn(self::RANK);
return self::doSelect($c);
}
 
static function getMaxRank()
{
$con = Propel::getConnection(self::DATABASE_NAME);
$sql = 'SELECT MAX('.self::RANK.') AS max FROM '.self::TABLE_NAME;
$stmt = $con->prepareStatement($sql);
$rs = $stmt->executeQuery();
 
$rs->next();
return $rs->getInt('max');
}

These methods will be of great use for both sorting interfaces. If you need more information about the way the Object Model handles database queries in symfony, check out the basic CRUD chapter of the Propel user guide.

There are two more method that needs to be added to the lib/model/Item.php class. They won't be needed here, but you will probably need them in a real world application, where you will also add and delete items to your table:

public function save($con = null)
{
// New records need to be initialized with rank = maxRank +1
if(!$this->getId())
{
$con = Propel::getConnection(ItemPeer::DATABASE_NAME);
try
{
$con->begin();
 
$this->setRank(ItemPeer::getMaxRank()+1);
parent::save();
 
$con->commit();
}
catch (Exception $e)
{
$con->rollback();
throw $e;
}
}
else
{
parent::save();
}
}
 
public function delete($con = null)
{
$con = Propel::getConnection(PagePeer::DATABASE_NAME);
try
{
$con->begin();
 
// decrease all the ranks of the page records of the same category with higher rank 
$sql = 'UPDATE '.ItemPeer::TABLE_NAME.' SET '.ItemPeer::RANK.' = '.ItemPeer::RANK.' - 1 WHERE '.ItemPeer::RANK.' > '.$this->getRank();
$con->executeQuery($sql);
// delete the item
parent::delete();
 
$con->commit();
}
catch (Exception $e)
{
$con->rollback();
throw $e;
}
}

Additions and deletions of records have to be managed carefully for the integrity of the rank field, that's why the save() and delete() methods are to be specialized. Because these methods do complex read/write operations and create a risk of concurrency issues, these operations are enclosed in a transaction (refer to the Propel documentation for more details about transactions in symfony).

Preparing the module

The interactions described in this tutorial will take place in a item module. Initialize it by calling (assuming you have a frontend application):

$ symfony init-module frontend item

Make sure your web server configuration is OK by testing the access to this new module via your favorite browser. Here is the URL that you should check if you follow this tutorial with a sandbox:

http://localhost/sf_sandbox/web/frontend_dev.php/item

Finally, if you want to test the ordering of items, you will need... items. Create a bunch of test items, either via a CRUD interface or a population file.

Now that everything is ready, let's get started.

Classic sortable list

A classic sortable list is a list for which each item has a control to change its order. First, create the action and template that display the list:

// add to modules/item/actions/actions.class.php
public function executeList()
{
$this->items = ItemPeer::getAllByRank();
$this->max_rank = ItemPeer::getMaxRank();
}
 
// create a template listSuccess.php in modules/item/templates/
<h1>Ordered list of items</h1>
<ul>
<?php foreach($items as $item): ?>
<li>
<?php
echo $item->getName().' ';
if($item->getRank() > 0):
echo link_to('Move up ', 'item/up?id='.$item->getId());
endif;
if($item->getRank() != $max_rank):
echo link_to('Move down', 'item/down?id='.$item->getId());
endif;
?>
</li>
<?php endforeach ?>
</ul>

The links to move an item up or down are displayed only when the reordering is possible. This means that the first item cannot be moved further up, and the last item cannot be moved further down. Check that the page displays correctly:

http://localhost/sf_sandbox/web/frontend_dev.php/item/list

Now, it's time to look into the item/up and item/down action. The up action is supposed to decrease the rank of the page given as a parameter, and to increase the rank of the previous page. The down action is supposed to increase the rank of the page given as parameter, and to decrease the rank of the following page. As they both do two write operations in the database, these actions should use a transaction.

The two actions have a very similar logic, and if you want to keep D.R.Y., you'd better find a smart way to write them without repeating any code. This is done by adding a swapWith() method to the Item.php model class:

public function swapWith($item)
{
$con = Propel::getConnection(ItemPeer::DATABASE_NAME);
try
{
$con->begin();
 
$rank = $this->getRank();
$this->setRank($item->getRank());
$this->save();
$item->setRank($rank);
$item->save();
 
$con->commit();
}
catch (Exception $e)
{
$con->rollback();
throw $e;
}
}

Then, the up and down actions become pretty simple:

public function executeUp()
{
$item = ItemPeer::retrieveByPk($this->getRequestParameter('id'));
$this->forward404Unless($item);
$previous_item = ItemPeer::retrieveByRank($item->getRank() - 1);
$this->forward404Unless($previous_item);
$item->swapWith($previous_item);
 
$this->redirect('item/list');
}
 
public function executeDown()
{
$item = ItemPeer::retrieveByPk($this->getRequestParameter('id'));
$this->forward404Unless($item);
$next_item = ItemPeer::retrieveByRank($item->getRank() + 1);
$this->forward404Unless($next_item);
$item->swapWith($next_item);
 
$this->redirect('item/list');
}

Read Full Tutorial