CakePHP paging and sorting on a Custom DataSource

CakePHP recommends that to use a web service API you should use a custom DataSource (here).
In their example on creating a Twitter DataSource, it doesn’t mention how to achieve paging and sorting.

Neil Crookes has a good example on his website, accessing Google Analytics data via REST and paging through it – however this involves modifying the cake core and overriding the pagination methods in the model.
The cake team do not look keen on this approach and rejected it when someone raised as a bug

The DataSource example by LoadSys includes paging, but sorting only works by specifying it in the controller, so it won’t work using the pagination helper in the view.

I’ve combined their approaches to work out how to allow paging and sorting in your DataSource without having to modify the other parts of you application, so it can work in just the same way as if you were using a database table.

1. Your DataSource needs a calculate method – this is where the SQL to calculate the number of rows would normally be generated, instead you can add a flag to your code to generate a count later on
2. Add caching to the DataSource read method, because cake will fire it twice, the first to get a count of the total available rows, the second time to request a page of the data. If you cache the data on the first request, the second request can just read from the cache
3. Add code to the DataSource read method to pick up the calculate request and return a count of how many rows in total are available
4. Make sure that the describe method exists and returns a meaningful schema – this is necessary for the sorting to work
5. Add methods to carry out the pagination or sorting

Expanding on the twitter example from the cake documentation. here is what you need to do in detail to enable paging and sorting.

1. In the datasources folder, create twitter_source.php and paste the example code in
2. In the models folder, create tweet.php and paste the example code in
3. In config/database.php paste the datasource definition in, substituting your login and password
4. In the controllers folder create twitter_controller.php and paste the following code, note that you aren’t doing anything differently in here than you would to access a database table:

   class TwitterController extends AppController {
   	var $name = 'Twitter';
   	var $uses = array('Tweet');
   	function index() {
   		$this->paginate = array(
   		'limit' => 2,);
   		$this->set('tweets',$this->paginate('Tweet'));
   	}
   }

5. The view requires a little more code to add the pagination and sorting controls, this is the same sort of code that would be generated by the scaffolding:

    counter(array( 'format' => __('Page %page% of %pages%, showing %current% records out of %count% total, starting on record %start%, ending on %end%', true) )); ?> 
   >
       
   
     
   
       
   sort('id');?>
       
   sort('text');?>
     
     
     
   
       
   
       
   link(__('View', true), array('action' => 'view', $item['Tweet']['id'])); ?>
     
     
   
    prev('<< '.__('previous', true), array(), null, array('class'=>'disabled'));?> | numbers();?> next(__('next', true).' >>', array(), null, array('class'=>'disabled'));?> 
   

Up until now, we have just been putting the example files into the correct locations and setting up the view. Now come the modifications to the DataSource.

Edit twitter_source.php and make the following changes:

1. Add a calculate method:

   function calculate(&$model, $func, $params = array()) {
   	return '__'.$func;
   }

2. Replace line 61 of the read method:

   $response = json_decode($this->connection->get($url), true);
   

   With the following to enable caching of the response

   $cachePath = 'tweet_'.md5($url);
   $response = cache($cachePath, null, '+1 minute');
   if ( !$response ) {
   	$response = $this->connection->get($url);
   	cache($cachePath, $response);
   }
   $response = json_decode($response, true);
   

3. Add a method to get a single page from the returned data:

   function __getPage($items = null, $queryData = array()) {
   		if ( empty($queryData['limit']) ) {
   			return $items;
   		}
   		$limit = $queryData['limit'];
   		$page = $queryData['page'];
   		$offset = $limit * ($page-1);
   		return array_slice($items, $offset, $limit);
   	}
   

4. Add a method to carry out sorting returned data:

   function __sortItems(&$model, $items, $order) {
   		if ( empty($order) || empty($order[0]) ) {
   			return $items;
   		}
   
   		$sorting = array();
   		foreach( $order as $orderItem ) {
   			if ( is_string($orderItem) ) {
   				$field = $orderItem;
   				$direction = 'asc';
   			}
   			else {
   				foreach( $orderItem as $field => $direction ) {
   					continue;
   				}
   			}
   
   			$field = str_replace($model->alias.'.', '', $field);
   
   			$values =  Set::extract($items, '{n}.'.$field);
   			if ( in_array($field, array('lastBuildDate', 'pubDate')) ) {
   				foreach($values as $i => $value) {
   					$values[$i] = strtotime($value);
   				}
   			}
   			$sorting[] = $values;
   
   			switch(low($direction)) {
   				case 'asc':
   					$direction = SORT_ASC;
   					break;
   				case 'desc':
   					$direction = SORT_DESC;
   					break;
   				default:
   					trigger_error('Invalid sorting direction '. low($direction));
   			}
   			$sorting[] = $direction;
   		}
   
   		$sorting[] = &$items;
   		$sorting[] = $direction;
   		call_user_func_array('array_multisort', $sorting);
   
   		return $items;
   	}
   

5. Add the following to the read method, above the final line (No. 70, return $results) to call the paging method, call the sorting method and return the item count:

   $results = $this->__getPage($results, $queryData);
   //return item count
   if ( Set::extract($queryData, 'fields') == '__count' ) {
   	return array(array($model->alias => array('count' => count($results))));
   }
   

That’s it. You can download the twitter_source and the view index to save a lot of cutting and pasting.

I’m sure there are many improvements to this that people can suggest – I’m new to CakePHP myself, but I hope you find it useful.