Drupal Tips - Tablesort/pagequery with Custom ORDER BY Clause |Tech Lull

drupal_version:

6.x

I have a table pulled from database results which uses the drupal api to allow for sortable columns and theming. This is accomplished with tablesort_sql, page_query, and theme 'pager' and 'table' calls. The issues I came across were: 1) wanting to have a custom ORDER BY clause 2) only having this clause on a certain column 3) having the pager disappear after the first two things were implemented I will list the code and then explain the parts.


$table_headers = array(
  		array('field' => 'course_code', 'data' => t('Course')), 
  		array('field' => 'class_name', 'data' => t('Course Description')), 
  		array('field' => 'location', 'data' => t('Location')), 
  		array('field' => 'start_date', 'data' => t('Start Date')), 

  );
   $current_header = tablesort_init($table_headers);
$sql = '';
  if($current_header['sql'] == 'start_date'){
   	$before = "IF(((SUBSTRING(`start_date`, 2, 1) = '-') OR 
(SUBSTRING(`start_date`, 3, 1) = '-' )),1, 1) 
".$current_header['sort'].", STR_TO_DATE(`start_date`,'%
%d-%%b-%%Y') ".$current_header['sort'].",";
				
   $sql = "SELECT * FROM {courses} 
".tablesort_sql($table_headers, $before);
  }else{
   $sql = "SELECT * FROM {courses} 
".tablesort_sql($table_headers);
  }

This is the table header information


$table_headers = array(
  		array('field' => 'course_code', 'data' => t('Course')), 
  		array('field' => 'class_name', 'data' => t('Course Description')), 
  		array('field' => 'location', 'data' => t('Location')), 
  		array('field' => 'start_date', 'data' => t('Start Date')), 
  );

tablesort_init returns an associative array of the current column, sort order - this is precisely what is needed for goal #2 - branching the ORDER BY clause for certain columns only


$current_header = tablesort_init($table_headers);

For goal #1, we pass our extra ORDER BY info as a second parameter to tablesort_sql


$before = "IF(((SUBSTRING(`start_date`, 2, 1) = '-') 
OR (SUBSTRING(`start_date`, 3, 1) = '-' )),1, 1) 
".$current_header['sort'].", STR_TO_DATE(`start_date`,'%
%d-%%b-%%Y') ".$current_header['sort'].",";
				
   $sql = "SELECT * FROM {courses} WHERE $where 
".tablesort_sql($table_headers, $before);

Keep in mind to omit the word ORDER BY and include the end comma.

I got the correct sort order at this point but an unexpected result was that the pager disappeared when sorting by that column.

After much googling and searching around drupal.org I found that my more human readable query which was more like:


$before = "
IF(
     (  (SUBSTRING(`start_date`, 2, 1) = '-') OR    
        (SUBSTRING(`start_date`, 3, 1) = '-' )
     ),1, 1) ".$current_header['sort'].
", 
STR_TO_DATE(`start_date`,'%%d-%%b-%%Y')".
$current_header['sort'].
",";

caused the problem - the drupal db api occasionally has problems with newline characters and extra whitespace. correcting this before passing the query string to the pager_query function solved problem #3

back to drupal tips...

Similar entries

Tablesort/pagequery with Custom ORDER BY Clause