Adding sortable columns to a table in a component

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Article Altered[edit]

Hopefully final fix after much head-banging upon desk Zola 03:38, 7 March 2010 (UTC)

Suggested Fix[edit]

There is a problem with this example. As reported in many posts, the column headings only allow DESCending sorts. In addition, the grid.sort method called by JHTML doesn't display the sort direction arrow graphic. If you can live without having the final sort column and sort direction stored in the user state for future use, the problem can be fixed as follows. The whole process is shown rather than alterations.

1. Add a new private variable to the MODEL. This is used to store the sort column field name and the sort direction.

var $_order  = array();

2. Add the following lines to the MODEL __construct() function. Values for filter_order and filter_order_Dir are pulled from the $_REQUEST array (if present) or default values substituted. Replace 'fieldName' with the name of the MySQL field name you want to sort by default. Change 'asc' to 'desc' if you want the default sort direction to be descending.

$this->_order[] = JRequest::getVar('filter_order', 'fieldName', 'POST', 'cmd');
$this->_order[] = JRequest::getVar('filter_order_Dir', 'asc', 'POST', 'word');

An example of a __construct() function already including the above lines may look like this, in case you don't have one already in your MODEL:

function __construct() 
	{
		$this->_order[] = JRequest::getVar('filter_order', 'Item_ID', 'POST', 'cmd');
		$this->_order[] = JRequest::getVar('filter_order_Dir', 'asc', 'POST', 'word');
		parent::__construct();
	}

3. Add a new private function to the MODEL to generate the necessary MySQL query suffix. The MODEL will need to call this function and append the result to any existing MySQL query before execution.

function _buildContentOrderBy() {
  return ' ORDER BY '.$this->_order[0].' '.$this->_order[1].' ';
 }

If, for example, the query was stored in a private variable called $_query like this:

$this->_query = "SELECT * FROM #_table";

Then the following would need to be added before the query is passed for execution:

$this->_query .= $this->_buildContentOrderBy();

Since the buildContentOrderBy() function will only work when it appends a mySQL query before execution this will NOT work:

$this->_query = mysql_query("SELECT * FROM #_table");
$this->_query .= $this->_buildContentOrderBy();

4. Add a new public function to the MODEL so that the ordering field and sort direction can be accessed by the VIEW using the get() method.

function getOrder() {
 return $this->_order;
}

5. Add the following lines to the display() function in the VIEW (usually view.html.php). This uses the JView::get() method to 'get' a copy of the array containing the ordering field and sort direction from the MODEL. The array is then assigned to the TEMPLATE.

$lists =& $this->get( 'order' );
$this->assignRef( 'lists', $lists );

6. To conform with HTML add the following to the TEMPLATE (usually default.php). This ensures that the Javascript necessary is placed between the the <head></head> tags. Change 'formName' to the name attribute of the <form> used later (see below)

<?php
 JFactory::getDocument()->addScriptDeclaration( "
 function tableOrdering( order, dir, task ) {
  var form = document.formName;
  form.filter_order.value = order;
  form.filter_order_Dir.value = dir;
  form.submit( task );
 }"
);
?>

7. Enclose the table to be sorted in <form></form> tags and add two hidden input fields as follows

<form action="index.php?option=com_componentName" method="post" name="formName">
 ...
 table goes here
 ...
 <input type="hidden" name="filter_order" value="<?php echo $this->lists[0]; ?>" />
 <input type="hidden" name="filter_order_Dir" value="<?php echo $this->lists[1]; ?>" />
</form>

Change the <form> action attribute as needed or use the JRoute method if you have the file router.php set up for SEF URLs.

8. For each of the columns you want to sort, replace each of the column headings with the following (enclosed in 'th' and '/th' tags). Change 'columnXName' to the table columns heading text you want and change fieldXName to the corresponding MySQL field names. Leave non-sortable columns unchanged.

<tr>
 <th><?php echo JHTML::_( 'grid.sort', 'column1Name', 'field1Name', $this->lists[1], $this->lists[0]); ?></th>
 <th><?php echo JHTML::_( 'grid.sort', 'column2Name', 'field2Name', $this->lists[1], $this->lists[0]); ?></th>
 <th><?php echo JText::_( 'unSortableColumnName'); ?></th>
 <th><?php echo JHTML::_( 'grid.sort', 'column3Name', 'field3Name', $this->lists[1], $this->lists[0]); ?></th>
</tr>

Preserving the state of 'filter_order' and 'filter_order_Dir' could be achieved by altering the MODEL __construct() function.

Fletcher 17:11, 11 October 2009 (UTC)

order clause in getListQuery() causing SQL syntax error in Joomla v1.7[edit]

I have been trying to add sortable columns using this page, and when I add the order clause,

$query->order($db->getEscaped($this->getState('filter_order', 'default_sort_column')) . ' ' . $db->getEscaped($this->getState('filter_order_Dir', 'ASC')));

I get a sql syntax error (MySQL 5.5). The display indicates that the order function has generated "ORDER BY" with nothing after it.

Any ideas on what's going wrong? I have all the other pieces in place, according to this document.

Bill

"In theory, there's no difference between theory and practice; in practice, there is" --Yogi Berra 15:21, 28 November 2011 (CST)

J1.5 talk

Adding sortable columns to a table in a component

From Joomla! Documentation

Article Altered[edit]

Suggested Fix[edit]

order clause in getListQuery() causing SQL syntax error in Joomla v1.7[edit]