Recently I had a requirement to write an interface that allows users to change order of elements and move them up and down. I would an interesting cookbook entry on symfony‘s website but it used propel as an ORM. Since my project uses doctrine, I had to implement the same in doctrine. Documenting this here so that someone else might find it useful.
So this is what we want to achieve.
Example of Changing order for elements
So first things first, let me define the schema that I am was using:
ReportXReportModule:
columns:
id: { type: integer(4), notnull: true, primary: true, autoincrement: true}
report_id: { type: integer(4), notnull: true}
report_module_id: { type: integer(4), notnull: true}
module_order: { type: integer(2), notnull: true}
The module_order is the field to be used to store the order of the module. So here’s adding a bunch of methods in the model class. So the requirement for this task is that the entry is also to be identified by the report. So in case you need to use these methods, you would have to adapt it to your requirement.
New methods in ReportXReportModuleTable.class.php
Fetch a report by Report ID and Order
public static function getByReportAndOrder($report_id, $module_order)
{
$data = Doctrine_Query::create()
->from('ReportXReportModule rrm')
->where('rrm.report_id = ?', $report_id)
->andWhere('rrm.module_order = ?', $module_order)
->fetchOne();
if (!$data) {
return false;
}
// send this back if this a sensible record
return $data;
}
For the given report find the highest value of order
public static function getMaxOrderByReport($report_id)
{
$order = 0;
if (!is_numeric($report_id)) {
return $order;
}
$record = Doctrine_Query::create()
->select('MAX(rrm.module_order)')
->from('ReportXReportModule rrm')
->where('rrm.report_id = ?', $report_id)
->fetchArray();
if (count($record) != 1) {
return $order;
} else {
if (isset($record[0]['MAX'])) {
$order = $record[0]['MAX'];
}
}
return $order;
}
New methods in ReportXReportModuleTable.class.php.
Get all the records for a given report ID and return them sorted by their order (ranking)
public static function getModulesByReport($report_id)
{
$records = array();
if (!is_numeric($report_id)) {
return $records;
}
$records = Doctrine_Query::create()
->from('ReportXReportModule rrm')
->where('rrm.report_id = ?', $report_id)
->leftJoin('rrm.ReportModule rm')
->orderBy('rrm.module_order asc')
->fetchArray();
return $records;
}
This is an interesting method, this is used to swap a record with another record. You shall see how this method is useful for the up and down actions that will follow shortly. This is also done within a transaction to ensure that the integrity of the order for modules is retained.
public function swapWith($module)
{
$conn = Doctrine_Manager::connection();
try {
$conn->beginTransaction();
$order = $this->getModuleOrder();
$this->setModuleOrder($module->getModuleOrder());
$this->save();
$module->setModuleOrder($order);
$module->save();
$conn->commit();
} catch (Doctrin_Exception $e) {
$conn->rollback();
}
}
This method is useful to delete a module. This not only just deletes the entry but also updates the order of the reset of the modules with a order higher than the current module.
public function deleteByReport($report_id)
{
$conn = Doctrine_Manager::connection();
try {
$conn->beginTransaction();
// decrease all the orders by 1 for all elements that have a rank greater than this rank
// execute custom sql to do that
$q = Doctrine_Query::create()
->update('ReportXReportModule rrm')
->set('rrm.module_order','rrm.module_order - 1' )
->where('rrm.module_order > ?', $this->module_order)
->execute();
$this->delete();
$conn->commit();
} catch(Doctrin_Exception $e) {
$conn->rollback();
}
}
Now that we have all the required methods in our model classes, its time to write the first action that will display all the modules. So the code snippet from the action is:
public function executeIndex(sfWebRequest $request)
{
// get the list of all the modules
$this->report_modules = ReportXReportModuleTable::getModulesByReport($this->current_report->getId());
$this->max_order = ReportXReportModuleTable::getMaxOrderByReport($this->current_report->getId());
}
and the corresponding template for that is:
<h2>List Of Current Modules</h2>
<?php if (count($report_modules)> 0) : ?>
<ol class="report-modules">
<?php foreach($report_modules as $module): ?>
<li>
<div class="name">
<?= $module['ReportModule']['name']?>
</div>
<div class="order">
<?php if ($module['module_order'] > 0) : ?>
<?= link_to('Up', 'reportmodulemanager/up?id=' . $module['id'])?>
<?php endif;?>
<?php if ($module['module_order'] != $max_order) : ?>
<?= link_to('Down', 'reportmodulemanager/down?id=' . $module['id'])?>
<?php endif;?>
</div>
<div class="action">
<?php if (1 == $module['ReportModule']['require_local_configuration']) : ?>
<?= link_to('Configure', 'reportmodulemanager/configure?id=' . $module['id'])?>
<?php endif;?>
<?= link_to('Remove', 'reportmodulemanager/delete?id=' . $module['id'])?>
</div>
</li>
<?php endforeach;?>
</ol>
<?php endif?>
As you can see each module has an up option only when its order is greater than 0. Similarly the module has a down option only when its order is not the same as the highest value for order
Now tying in the up and down actions.
public function executeUp(sfWebRequest $request)
{
$reportmodule = Doctrine::getTable('ReportXReportModule');
$item = $reportmodule->find($request->getParameter('id'));
$this->forward404Unless($item);
$previous_item = ReportXReportModuleTable::getByReportAndOrder(
$this->current_report->getId(),
($item->getModuleOrder() - 1)
);
$this->forward404Unless($previous_item);
$item->swapWith($previous_item);
$this->redirect('reportmodulemanager/index');
}
and the for the down action:
public function executeDown(sfWebRequest $request)
{
$reportmodule = Doctrine::getTable('ReportXReportModule');
$item = $reportmodule->find($request->getParameter('id'));
$this->forward404Unless($item);
$next_item = ReportXReportModuleTable::getByReportAndOrder(
$this->current_report->getId(),
($item->getModuleOrder() + 1)
);
$this->forward404Unless($next_item);
$item->swapWith($next_item);
$this->redirect('reportmodulemanager/index');
}
As you can see we swap a module with one after it for the Up action, similarly we swap a module with one after it for the down action.
And that’s pretty much it to have a list that can be sorted. The other interesting actions would be when you add a new record or remove on. At this point you must have figured out that adding a new record should be pretty straight forward as you have now methods to access the highest rank using the method ReportXReportModuleTable::getMaxOrderByReport
As a part of this post, one day I shall add how to progressively enhance so give it a fancy drag and drop interface as suggested in the original article. However if you have any questions or suggestions to make this better, please feel free to leave a note below.
