Actions

J1.5

Difference between revisions of "Adding sortable columns to a table in a component"

From Joomla! Documentation

(Step 1: The model)
(Updated code to Joomla 1.7)
Line 5: Line 5:
 
==Step 1: The Model==
 
==Step 1: The Model==
  
The first thing is to add the filter_order and filter_order_Dir request into the __construct() function. If you've used JPagination, this step will already be familiar to you. Change 'default_column_name' to the name of the column you want to use as the default sort, and change the filter order direction if you wish  Adding this information to the  State object here insures it's accessible to all of the code that might need it.
+
The first thing is to add the filter_order and filter_order_Dir request populateState() method of your model. Change 'default_column_name' to the name of the column you want to use as the default sort, and change the filter order direction if you wish  Adding this information to the  State object here insures it's accessible to all of the code that might need it.
  
 
<source lang="php">
 
<source lang="php">
function __construct()
+
public function populateState() {
{
+
$filter_order = JRequest::getCmd('filter_search');
parent::__construct();
+
$filter_order_Dir = JRequest::getCmd('filter_order_Dir');
 
+
global $mainframe, $option;
+
 
+
$filter_order     = $mainframe->getUserStateFromRequest( $option.'filter_order', 'filter_order', 'default_column_name', 'cmd' );
+
$filter_order_Dir = $mainframe->getUserStateFromRequest( $option.'filter_order_Dir', 'filter_order_Dir', 'asc', 'word' );
+
 
+
 
$this->setState('filter_order', $filter_order);
 
$this->setState('filter_order', $filter_order);
 
$this->setState('filter_order_Dir', $filter_order_Dir);
 
$this->setState('filter_order_Dir', $filter_order_Dir);
Line 23: Line 18:
  
  
In the model which generates the data that will form the table, you need to make a change to the method which builds the database query that will be used to populate the table.  Most often this is the [[JModel/getData|getData()]] method, but might not be; check in the view to see which method is actually being used.
+
In the model which generates the data that will form the table, you need to make a change to the method which builds the database query that will be used to populate the HTML table.  Most often this is the [[JModelList::getListQuery|getListQuery()]] method, but might not be.
  
 
The model could pull data in from anywhere; it doesn't have to be a database, but in the vast majority of cases the model will be using the Joomla database API to submit SQL queries to a database.  Assuming that to be the case, you need to adjust the query so that the sort parameters are taken into account.
 
The model could pull data in from anywhere; it doesn't have to be a database, but in the vast majority of cases the model will be using the Joomla database API to submit SQL queries to a database.  Assuming that to be the case, you need to adjust the query so that the sort parameters are taken into account.
Line 30: Line 25:
  
 
<source lang="php">
 
<source lang="php">
function _buildContentOrderBy()
+
public function getListQuery() {
{
+
$db = JFactory::getDbo();
global $mainframe, $option;
+
$query = $db->getQuery(true);
 
+
$orderby = '';
+
// ...
$filter_order    = $this->getState('filter_order');
+
$filter_order_Dir = $this->getState('filter_order_Dir');
+
$query->order($db->getEscaped($this->getState('filter_order', 'default_sort_column')) . ' ' . $db->getEscaped($this->getState('filter_order_Dir', 'ASC')));
+
/* Error handling is never a bad thing*/
+
return $query;
if(!empty($filter_order) && !empty($filter_order_Dir) ){
+
$orderby = ' ORDER BY '.$filter_order.' '.$filter_order_Dir;
+
}
+
+
return $orderby;
+
 
}
 
}
 
</source>
 
</source>
 +
 +
Replace 'default_sort_column' with the column you want to sort by by default.  You can also change 'ASC' to 'DESC', depending on which way you want to sort by default.
  
 
==Step 2: The View==
 
==Step 2: The View==
Line 52: Line 44:
 
To do this you need to add a few lines of code to your view file, typically view.html.php with code similar to this:
 
To do this you need to add a few lines of code to your view file, typically view.html.php with code similar to this:
 
<source lang="php">
 
<source lang="php">
function display($tpl = null)
+
public function display($tpl=null) {
{
+
$items = $this->get('Items');
 
+
$state = $this->get('State');
// Get data from the model
+
$items = & $this->get( 'Data');
+
$this->sortDirection = $state->get('filter_order_Dir');
$this->assignRef('items', $items);
+
$this->sortColumn = $state->get('filter_order');
+
/* Call the state object */
+
parent::display($tpl);
$state =& $this->get( 'state' );
+
}
 
+
/* Get the values from the state object that were inserted in the model's construct function */
+
$lists['order_Dir'] = $state->get( 'filter_order_Dir' );
+
$lists['order']    = $state->get( 'filter_order' );
+
 
+
$this->assignRef( 'lists', $lists );
+
 
+
parent::display($tpl);
+
}
+
  
 
</source>
 
</source>
Line 93: Line 76:
 
.... table goes here ....
 
.... table goes here ....
  
<input type="hidden" name="filter_order" value="<?php echo $this->lists['order']; ?>" />
+
<input type="hidden" name="filter_order" value="<?php echo $this->sortColumn; ?>" />
<input type="hidden" name="filter_order_Dir" value="<?php echo $this->lists['order_Dir']; ?>" />
+
<input type="hidden" name="filter_order_Dir" value="<?php echo $this->sortDirection; ?>" />
 
</form>
 
</form>
 
</source>
 
</source>
Line 108: Line 91:
 
<source lang="php">
 
<source lang="php">
 
<tr>
 
<tr>
<th><?php echo JHTML::_( 'grid.sort', 'Name', 'DbNameColumn', $this->lists['order_Dir'], $this->lists['order']); ?></th>
+
<th><?php echo JHTML::_( 'grid.sort', 'Name', 'DbNameColumn', $this->sortDirection, $this->sortColumn); ?></th>
<th><?php echo JHTML::_( 'grid.sort', 'Description', 'DbDescriptionColumn', $this->lists['order_Dir'], $this->lists['order']); ?></th>
+
<th><?php echo JHTML::_( 'grid.sort', 'Description', 'DbDescriptionColumn', $this->sortDirection, $this->sortColumn); ?></th>
 
</tr>
 
</tr>
 
</source>
 
</source>

Revision as of 14:20, 23 August 2011

Replacement filing cabinet.png
This Namespace has been archived - Please Do Not Edit or Create Pages in this namespace. Pages contain information for a Joomla! version which is no longer supported. It exists only as a historical reference, will not be improved and its content may be incomplete.

Given that you have a table of data already in your component, how do you make some, or all, of the table columns sortable, like many of them are in the Joomla Administrator? It's not particularly hard to do, but there are several steps required and details you need to be aware of so everything fits together properly. There are variations on the procedure given here and once you are confident that you understand how it all works you should feel free to explore other possibilities that may suit your purposes better.

This procedure assumes that your component is structured according to the Model-View-Controller (MVC) design pattern. The general idea behind the procedure will still be applicable to non-MVC components if you apply a bit of imagination!

Contents

Step 1: The Model

The first thing is to add the filter_order and filter_order_Dir request populateState() method of your model. Change 'default_column_name' to the name of the column you want to use as the default sort, and change the filter order direction if you wish Adding this information to the State object here insures it's accessible to all of the code that might need it.

public function populateState() {
        $filter_order = JRequest::getCmd('filter_search');
        $filter_order_Dir = JRequest::getCmd('filter_order_Dir');
 
        $this->setState('filter_order', $filter_order);
        $this->setState('filter_order_Dir', $filter_order_Dir);
}


In the model which generates the data that will form the table, you need to make a change to the method which builds the database query that will be used to populate the HTML table. Most often this is the getListQuery() method, but might not be.

The model could pull data in from anywhere; it doesn't have to be a database, but in the vast majority of cases the model will be using the Joomla database API to submit SQL queries to a database. Assuming that to be the case, you need to adjust the query so that the sort parameters are taken into account.

This information gets called by whatever function builds the ORDER BY clause, typically a private function like this:

public function getListQuery() {
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
 
        // ...
 
        $query->order($db->getEscaped($this->getState('filter_order', 'default_sort_column')) . ' ' . $db->getEscaped($this->getState('filter_order_Dir', 'ASC')));
 
        return $query;
}

Replace 'default_sort_column' with the column you want to sort by by default. You can also change 'ASC' to 'DESC', depending on which way you want to sort by default.

Step 2: The View

Having generated the sorting variables in the model, you need to assign them to the view so that they show up on the page when it is displayed.

To do this you need to add a few lines of code to your view file, typically view.html.php with code similar to this:

public function display($tpl=null) {
        $items = $this->get('Items');
        $state = $this->get('State');
 
        $this->sortDirection = $state->get('filter_order_Dir');
        $this->sortColumn = $state->get('filter_order');
 
        parent::display($tpl);
}

Step 3: The Template

Now you need to add some elements to the component layout file. The table must be included in a form. This might already be the case as, for example, you might already have implemented pagination or filtering on the table. But if the table is not yet in a form then now is the time to wrap it in <form> and </form> tags. The reason that a form is required is that the sortable columns rely on a bit of JavaScript that will submit the form with sort parameters added. Naturally, this will involve a page load, so if you would prefer an AJAX-based solution, then this procedure is not for you.

The form tags will look something like this:

<form id="adminForm" action="<?php echo JRoute::_( 'index.php' );?>" method="post" name="adminForm">
 
.... table goes here ....
 
</form>

Notice that the form name must be "adminForm". You may need to adjust the action URL in some cases.

You also need to add a couple of hidden fields to the form. They can be placed anywhere between the <form> and </form> tags, but generally they are placed just before the closing tag, like this:

<form id="adminForm" action="<?php echo JRoute::_( 'index.php' );?>" method="post" name="adminForm">
 
.... table goes here ....
 
<input type="hidden" name="filter_order" value="<?php echo $this->sortColumn; ?>" />
<input type="hidden" name="filter_order_Dir" value="<?php echo $this->sortDirection; ?>" />
</form>

Now look at the table itself. You might have a table with static headings already, looking vaguely like this:

<tr>
        <th>Name</th>
        <th>Description</th>
</tr>

You need to replace the static column names with calls to the Joomla JHTML static class, so that your code will look something like this:

<tr>
        <th><?php echo JHTML::_( 'grid.sort', 'Name', 'DbNameColumn', $this->sortDirection, $this->sortColumn); ?></th>
        <th><?php echo JHTML::_( 'grid.sort', 'Description', 'DbDescriptionColumn', $this->sortDirection, $this->sortColumn); ?></th>
</tr>

You will definitely need to adapt this code to your specific requirements. The arguments to the JHTML call are as follows:

  1. Must be 'grid.sort' so that JHTML will insert the correct behaviour for a sortable column.
  2. This is the name of the column that your visitors will actually see. You need to change this for your particular table columns.
  3. This is the name of the corresponding database field (column) that is to be sorted on. This will be passed to the model, most likely so it can be added to an "ORDER BY" clause in the SQL query statement.
  4. Must be exactly as shown here. It is the current order direction (ascending or descending) and comes from the view (see later).
  5. Must be exactly as shown here. It is the name of the column that the table is currently sorted on and comes from the view (see later).

In short, you need to amend the second and third arguments to each JHTML call appropriately.

Finally, if your sortable table is going to be in the front-end of your site, then you need to add a little snippet of JavaScript to the layout. Alternatively, you can add it to the view code (using JDocument->addScriptDeclaration) if you would rather keep your JavaScript code in the HTML <head> section.

<script language="javascript" type="text/javascript">
function tableOrdering( order, dir, task )
{
        var form = document.adminForm;
 
        form.filter_order.value = order;
        form.filter_order_Dir.value = dir;
        document.adminForm.submit( task );
}
</script>

You don't need to add this code if your sortable table is in the Administrator as this code is loaded for you automatically anyway.

This completes the changes you need to make to the layout. JHTML grid.sort will now add a call to the tableOrdering function so that tableOrdering will be called whenever the user clicks on the column header. tableOrdering puts the name of the column that was clicked, and the sort direction, into the hidden form fields and submits the form. In the next step you will see how the model picks up those field values and amends the database query appropriately.

Step 4: Styling the result

Finally. you might want to apply a bit of CSS styling to make the output a bit more attractive.

Selecting the sortable columns can only be done via their context, so you will probably need to add a CSS class to the <table>, the <tr> or to the <th> tags. This is what the output might look like with a class added to the tag:

<tr class="sortable">
        <th><a href="javascript:tableOrdering('DbName','asc','');" title="Click to sort by this column">Training provider</a></th>
        <th><a href="javascript:tableOrdering('DbDescription','asc','');" title="Click to sort by this column">Location</a></th>
</tr>

To add a bit of space between the column name and the ascending/descending indicator image (a common requirement), you could then apply CSS like this:

tr.sortable th img {
        margin-left: 5px;
}

See also