Dojo Grid with CakePHP

JavaScript grid is an advanced replacement for the standard HTML table element. Several Rich Internet Application (RIA) toolkits (Dojo, Ext, Flex, etc.) provide their own data grid components with various advanced features, like sorting and in-place editing. I'll outline in this tutorial how to use Dojox DataGrid together with a popular PHP framework — CakePHP.

Have a look at the two screen shots below. They demonstrate one important aspect of a grid component — virtual scrolling. It means that the scrollbar replaces previous/next buttons, enabling you to access all the data conveniently using a scrollbar, just like you would do it in Excel. In addition to improving usability virtual scrolling is also a performance optimization technique. instead of creating the whole grid up front, it's created little-by-little as the user scrolls down. The grid handles paging internally and fetches data with Ajax queries in real time. Note that if you're looking for the familiar next-button user interface, it's also possible to mix a grid with a paging controller.

CakePHP standard index table Dojo grid for cakephp user index
A standard CakePHP index page creates a HTML table with paging info above it and paging controller below it. In a Dojo grid the same user data is accessible using a vertical scrollbar.

Grid Introduction
If you don't yet have any experience from Dojo Grid, I recommend taking a look at the following resources. There are several samples included in the Dojo source, there's the official documentation and Sitepen provides some articles.

Versions
Article was originally written for PHP 5.2, MySQL 5.0, CakePHP 1.2, Dojo 1.3. and updated on 7 Jan 2010 to work also with CakePHP 1.3 and Dojo 1.5.

Set up new database

Connect to database (mysql -u root -p) to create database, user and schema:

CREATE DATABASE cake_demo_grid;
USE cake_demo_grid;

CREATE USER 'cake_demo'@'localhost' IDENTIFIED BY '41Ph4b374';
GRANT ALL PRIVILEGES ON cake_demo_grid.* TO 'cake_demo'@'localhost';

CREATE TABLE `users` (
  `id` int(10) NOT NULL auto_increment,
  `username` varchar(30) collate utf8_unicode_ci NOT NULL,
  `password` varchar(50) collate utf8_unicode_ci NOT NULL,
  `first_name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `birthday` date default NULL,
  `created` datetime default NULL,
  `updated` datetime default NULL,
  `email` varchar(255) collate utf8_unicode_ci NOT NULL,
  `phone` varchar(20) collate utf8_unicode_ci NOT NULL,
  `active` int(2) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Once done add some pre-generated test data to your database to see the grid in action.

Set up new Cake project

There are several ways to create a new CakePHP project - while some like to start to work directly with the app directory, I'd recommend you to generate the foundation for the new project by using Cake's command line tools. If you're not yet familiar with "baking", have a look at this tutorial: CakePHP Bake – Baking Models, Controllers and Views the CakePHP 1.2 Way. Once you know the concept, continue with this step-by-step walk-through.

Move to the directory where you installed cake and create a new stub project:

cd cake
cake bake project grid_demo

Move to the new project directory and set up database connection:

cd grid_demo
cake bake db_config

This will start a console program where you're asked to fill in database details:

Database Configuration:
---------------------------------------------------------------
Name:  
[default] > default
Driver: (db2/firebird/mssql/mysql/mysqli/odbc/oracle/postgres/sqlite/sybase) 
[mysql] > mysqli
Persistent Connection? (y/n) 
[n] > n
Database Host:  
[localhost] > 
Port?  
[n] > 
User:  
[root] > cake_demo
Password:  
> 41Ph4b374
Database Name:  
[cake] > cake_demo_grid
Table Prefix?  
[n] >  
Table encoding?  
[n] > utf8

Now you're ready to create the basic structure for your program:

cake bake all

Once done you can check out that it works by visiting the freshly baked index page on your development server: http://localhost/cake/grid_demo/users/index. On this page you should see test users in a table. CakePHP builds in paging and sorting automatically, but these actions require a page fresh.

Prepare CakePHP

Before we get started with the grid, we need to make some preparations. Since CakePHP doesn't handle json file endings out-of-the-box, we need to set up routing by adding the following three changes/files.

1. app_controller.php

var $helpers = array('Html', 'Session', 'Js', 'Javascript');

var $components = array('RequestHandler'); 

function beforeFilter() { 
 	$this->RequestHandler->setContent('json', 'text/x-json'); 
}

2. config/routes.php

Router::parseExtensions('json');

3. views/layouts/json/default.ctp

<?php echo $content_for_layout; ?>

Additionally Cake applies it's stylesheets in a too eager fashion. Let's fix it by changing all table related rules, such as:

table tr td {/* declarations */}

... to apply only for the original index page, not for the grid page:

.index table tr td {/* declarations */}

Set up Grid

Compared to the standard HTML table Dojo Grid adds here is a ability to go through enormous data sets just by scrolling through the grid. It handles fetching the right data on the background through standard interfaces. Dojo Grid connects to the server backend using a data store that provides a standard interface to different kinds of data sources. In this simple example we'll use ItemFileReadStore that reads JSON type of data and handles paging and sorting locally.

For a grid you basically need two actions - one that creates the grid and another one that server the data. (In many web MVC frameworks actions are methods defined in controller classes. A user action such as a page request is mapped to and invokes an action method.) These actions are really simple:

controllers/users_controller.php

/**
 * Additional index page for grid view
 * 
 * This is a static page that defines and creates Grid using Dojo.
 * Grid will fetch data from the index_grid_data.json action.
 */
function index_grid() {
	
}

/**
 * Index data in JSON format for Dojo grid
 * 
 * Instead of just adding JSON view for the original index action, this
 * is a separate action because we might want to reformat some pagination
 * parameters, etc.
 */
function index_grid_data() {
	// remove CakePHP debug info from screwing up the JSON response
	// Configure::write('debug', 0);
	
	$this->User->recursive = 0;
	
	// since ItemFileReadStore handles sorting and paging on the client-side,
	// we don't paginate here, but instead serve everything.
	$this->set( 'users', $this->User->find( 'all' ) );
}

Then the corresponding view files for these actions. Let's start by creating the grid - it consists of three parts: 1) fetching JavaScript files, 2) creating a data store and 3) creating the grid itself. The needed CSS and JS files could be included in the project layout/theme files, but in the following sample, for the sake of simplicity, I have included all of them on the view template. Dojo allows us to create components programmatically (the usual: var x = new X();) and also declaratively (dojo specific markup attributes (dojoType) are parsed and found widgets are instantiated). In this sample the data store is created programmatically in JavaScript and the grid is declared in markup. (If you prefer to do it all programmatically, have a look at the samples mentioned in the info box at the beginning of the tutorial.)

views/users/index_grid.ctp

<?php
// create a dojo data grid that will fetch and display user data from
// "index_grid_data" action

// all included CSS and JS here have the last param set to false, which
// means they're non-inline, i.e. they'll be placed into head element

// we're using google's CDN for this demo.
// for production use you'd make your own dojo build.
$dojo_dir = "http://ajax.googleapis.com/ajax/libs/dojo/1.5/";

$html->css( $dojo_dir . "dojo/resources/dojo.css", "stylesheet", array('inline' => false) );
$html->css( $dojo_dir . "dijit/themes/tundra/tundra.css", "stylesheet", array('inline' => false) );
$html->css( $dojo_dir . "dojox/grid/resources/tundraGrid.css", "stylesheet", array('inline' => false) ); 

$javascript->link( $dojo_dir . "dojo/dojo.xd.js", false );

// load dependencies, instantiate data store and parse widgets
// declared in markup
$javascript->codeBlock("
  dojo.require('dojox.grid.DataGrid');
  dojo.require('dojo.data.ItemFileReadStore');
  
  var jsonStore; // global
  
  // addOnLoad executes when all the dependencies have downloaded
  dojo.addOnLoad( function() {
    console.log( '[index_grid] Dependencies loaded, creating store.' );
    // store is passed via a global reference (jsonStore) to the grid
    jsonStore = new dojo.data.ItemFileReadStore({ url: 'index_grid_data.json' });
    dojo.parser.parse();
  } );
", array( 'inline' => false ));

?>
<div class="users index_grid tundra">
  <h2><?php __('Users');?></h2>
  
  <table id="gridNode" jsId="grid" dojoType="dojox.grid.DataGrid" query="{ id: '*' }"
    store="jsonStore" style="width: 900px; height: 200px">
    <script type="dojo/connect">
      // DojoML "dojo/connect" without event attribute
      // fires when widget is initiated
      console.log( '[index_grid/Grid] Initiating Grid with ' +
        'store: %o and query: %o.', this.store, this.query );
    </script>
    <thead>
      <tr>
        <th field="id" width="20px"><?php __('id');?></th>
        <th field="username" width="auto"><?php __('username');?></th>
        <th field="first_name" width="auto"><?php __('first_name');?></th>
        <th field="last_name" width="auto"><?php __('last_name');?></th>
        <th field="birthday" width="auto"><?php __('birthday');?></th>
        <th field="created" width="auto"><?php __('created');?></th>
        <th field="email" width="auto"><?php __('email');?></th>
        <th field="phone" width="auto"><?php __('phone');?></th>
      </tr>
    </thead>
  </table>
</div>

And then the one that serves JSON-formatted data to our grid:

views/users/json/index_grid_data.ctp

<?php
// Cakephp returns data with unnecessary table name layer - flatten that tree
$users = Set::extract($users, '{n}.User');

// Dojo grid expects data in items property - wrap it
// Note: the object method will convert the array into a json string
echo $javascript->object(
	array(
		'items' => $users,
		'identifier' => 'id'
	)
);
?>

Note that since we're just adding another presentation, we don't need to touch the model.

That's it - you should be able to view user data in a grid on your server at: http://localhost/cake/grid_demo/users/index_grid

11 comments

 
Manuel Güereca #1 24 Nov 2010 18:25

The code in CakePHP 1.3 seems not work!

 
Juho #2 24 Nov 2010 21:30

Yes, it's currently only for CakePHP 1.2 and Dojo 1.3. I plan to update it later for CakePHP 1.3 and Dojo 1.5.

 
Manuel Güereca #3 24 Nov 2010 23:21

ok thanks!

 
Chris #4 2 Jan 2011 05:42

Thank you for your excellent tutorial. I would also like to try this within CakePHP 1.3 using Dojo 1.5; I'm looking forward to the update!

 
Juho #5 8 Jan 2011 01:34

I updated the code samples so that it works now on both CakePHP 1.2 and 1.3. Please, let me know if you bump into any further issues.

 
Chris #6 8 Jan 2011 15:48

I have tried the example and although my page loads with the column headers, no data is retrieved and an error appears saying "Sorry, an error occurred".

 
Juho #7 8 Jan 2011 16:19
It happens when the json data isn't properly formatted. You can check from Firebug what the grid loaded or alternatively you can go directly to URL http://localhost/cake/grid_demo/users/index_grid_data.json. It should look like this:
{"items":[{"id":"1","username":"Priscilla","password":"WKC71KFB6XQ",
"first_name":"Caleb","last_name":"Bauer","birthday":"1957-06-17","created":
"2009-12-09 00:28:10","updated":"2009-12-10 04:53:41"
,"email":"Phasellus.elit.pede@example.com","phone":
"1 88 603 1028-5832","active":"1"},{"id":"2","username":"Piper","password":
"JNY77AKQ6OR","first_name":"Gretchen","last_name":"Cline","birthday":
"1980-11-07","created":"2009-12-11 07:28:07","updated":"2009-12-09 08:05:33",
"email":"dapibus@example.com","phone":"1 68 369 5543-8716",
"active":"0"}],"identifier":"id"} 
If there's anything extra, you can try to switch on Configure::write('debug', 0); in users_controller.php.
 
Chris #8 8 Jan 2011 17:28

Here is my JSON response (Firebug and direct URL):

Array{"items":[{"id":"10"}],"identifier":"id"}

(I've simplified matters by changing the query to ask only for the ID, and just the first record.)

 
Juho #9 9 Jan 2011 20:37

There's the word "Array" in the beginning that invalidates the JSON structure. It should start and end with { ... }. This "Array" might come from some debug statement such as echo $users; - be sure to remove any such statements.

 
Chris #10 10 Jan 2011 00:08

You're absolutely right Juho!

If anyone finds it useful, I used a JSON formatter at "http://jsonformatter.curiousconcept.com/" which indicted that my JSON data was invalid with the "Array" keyword, and valid without it. As Juho precisely asserted, I eventually noticed "echo $users;" in my "index_grid_data.ctp" view file.

When I had earlier navigated to "cake/users/json/index_grid_data.json" the browser output was "Array { ... }" which I then thought to be correct JSON. Two things should have raised alarm bells: 1) the presence of the word "Array"; 2) the fact it was echoing anything at all!

All of this has actually helped me to better understand Dojo, Ajax and JSON.

Many thanks for your help!

 
Chris #11 8 Jan 2011 19:57

Also, Firebug reports the following error:

SyntaxError: missing ) in parenthetical

(function(){var _1=null;if((_1||(typeo...setTimeout(dojo._loadInit,100);}})();

dojo.xd.js (line 14)