Using remote filter, sort, and groups in Stores, with PHP code

Was working with stores today, and wrote some Yii PHP code to support their features. Here is what I wanted to do:

1. support loading a single model via Model.load()
2. support combobox autocomplete
3. support Store remote paging, and filtering, sorting, and grouping by multiple properties
4. all using the same URL
5. avoid SQL injection

You need to be careful with custom filtering, sorting, and grouping on your server, as it is easy to allow SQL injection.

Here is what the requests and responses look like. Code is at the bottom.

1. Model.load() requests the model by id...

Request:

index?id=3

JSON Response:

{
  "success":true,
  "data":{model data...}
}

or, if it can't find the model:

{
  "success":false,
  "errors":"Not Found"
}

We don't need the total count, as we are not using a Store

2. combobox autocomplete requests the models via the query parameter, as well as start and limit for paging

Request:

index?query=McGu&page=1&start=0&limit=25

Response:

{
  "success":true,
  "total":101,
  "data":[{model data}, {model data}, ...]
}

I am not sure why there is a page parameter along with start and limit. I chose to ignore the page parameter.

The total property shows how many records match the query, while you might send back only 25 if using paging. Note that the data property is now an array

Query is sort of like filter, though filter is intended to be on a particular property, and query could be on multiple properties, and is more free form

(Responses below are omitted as they are the same format as the above)

To turn on remote filter, sort, and grouping on a store, define your store like this:

Ext.define('My.store.Contacts', {
  extend:'Ext.data.Store',
  model:'My.model.Contact',
  remoteSort:true,
  remoteFilter:true,
  remoteGroup:true
});

3. Store paging uses the page parameter, and the start and limit parameters.

Request:

index?page=1&start=0&limit=25

3a. Store filtering uses the filter parameter and looks like this:

Request:

index?filter=[{"property":"first_name","value":"Neil"},{"property":"city","value":"Vancouver"}, ...]

as you can see, the filter parameter is a JSON array

3b. Store grouping looks like this:

index?group=[{"property":"country","direction":"ASC"},{"property":"province","direction":"ASC"}, ...]

You can group by multiple fields, or by one or none.

3c. Store sorting has two modes. simpleSortMode looks like this:

index?sort=email&dir=DESC

where dir is the direction of the sort

and, uh, not simpleSortMode, looks like this:

index?sort=[{"property":"email","direction":"DESC"},{"property":"last_name","direction":"ASC"}, ...]

So, you can sort by multiple fields in different directions if you want

You can change the sort mode by setting store.getProxy().simpleSortMode = true | false;

You can of course use filtering, sorting, grouping, and paging all at the same time

These are the function calls to perform the above:

myStore.filter('city', 'vancouver');
myStore.sort('last_name');   
myStore.group('country');

Here is the Yii PHP code to handle all of these requests and return database data to the client:

class ContactsController extends CController {

  public function actionIndex($id = null, $query = null, $filter = null, $sort = null, $dir = 'ASC', $start = 0, $limit = 25, $group = null) {

    /* the json property that contains the model(s): */
    $root = 'contacts';
    
    /* if request id, send back a single model: */
    if($id !== null){
      $model = $this->loadModel($id);
      header('Content-type:application/json');
      echo CJSON::encode(array(
   'success'=>true,
   $root=>$model
      ));
      exit();
    }
    
    $criteria = new CDbCriteria(array(
  'limit' => $limit,
  'offset' => $start
     ));
    
    /* let the user search first and last name via a combobox */
    if ($query !== null) {
      $criteria->condition = 'first_name LIKE :query OR last_name LIKE :query';

      /* use PDO parameters to avoid sql injection: */
      $criteria->params[':query'] = "$query%";
    }    

    /*safely apply group by parameters (only allow group property names that are on the model)*/
    $grouper = new ExtGrouper('Contact');
    $grouper->applyGroups($criteria);
    
    /*safely apply filters*/
    $filter = new ExtFilter('Contact');
    $filter->applyFilters($criteria);
    
    /*safely apply order by parameters*/
    $sorter = new ExtSorter('Contact');
    $sorter->applyOrder($criteria);

    header('Content-type:application/json');
    echo CJSON::encode(array(
 'contacts' => Contact::model()->findAll($criteria),
 'total' => Contact::model()->count($criteria), /* this query does a count(*) */
 'success' => true
    ));
    exit();
  }

  private function loadModel($id) {
    $model = Contact::model()->findByPk((int) $id);
    if ($model === null) {
      header('Content-type:application/json');
      echo CJSON::encode(array(
   'success' => false,
   'errors' => '404 Not Found'
      ));
      exit();
    } else {
      return $model;
    }
  }
}

Here is the code for ExtSorter. It ignores any sort->property that is not on the database model, which is safer. The other classes are very similar.

class ExtSorter {

  public $modelClass;
  public $sortVar = 'sort';

  public function __construct($modelClass) {
    $this->modelClass = $modelClass;
  }

  public function applyOrder($criteria) {
    $order = $this->getOrderBy();
    if (!empty($order)) {
      if (!empty($criteria->order))
 $criteria->order.=', ';
      $criteria->order.=$order;
    }
  }

  public function getOrderBy() {
    $attributeNames = CActiveRecord::model($this->modelClass)->attributeNames();
    if (isset($_GET[$this->sortVar])) {
      $sorters = json_decode($_GET[$this->sortVar], false);
      if($sorters === null){
 $sort = $_GET[$this->sortVar];
 $dir = isset($_GET['dir'])?$_GET['dir']:'ASC';
 $sorters = json_decode("[{\"property\":\"$sort\", \"direction\":\"$dir\"}]", false);
      }
      $sb = array();
      foreach ($sorters as $sorter) {
 if (in_array(strtolower($sorter->property), $attributeNames) && in_array(strtolower($sorter->direction), array('asc', 'desc'))) {
   $sb[] = "$sorter->property $sorter->direction";
 }
      }
      return implode(', ', $sb);
    }
    else
      return '';
  }

}