Difference between #12 and #11 of Display an AJAX tree from your DB using CTreeView

unchanged
Title
Display an AJAX tree from your DB using CTreeView
unchanged
Category
Tutorials
unchanged
Tags
AJAX, CTreeView
changed
Content
## The database

This example uses a MySQL DB with a table named `tree` with the fields `id`,
`name`, and `parent_id`. The `parent_id` will be `NULL` for root elements. The
SQL is kept simple (no autoinc, no FK, etc).

~~~~
[sql]
CREATE TABLE tree (
  id INTEGER UNSIGNED NOT NULL,
  name VARCHAR(50) NOT NULL,
  parent_id INTEGER UNSIGNED,
  PRIMARY KEY (id)
)
~~~~

MySQL has no recursive queries. In order to fetch tree nodes, you have to
recursively send SQL queries for each node, asking for its children. The easiest
way to do so is to send these queries with AJAX, so that the tree can be
displayed even if the deep nodes haven't been fetched yet. If you're using
Oracle or Postgresql, there are other solutions, though this will also work.

## The view

In your view, add:

~~~~
[php]
<?php
$this->widget(
	'CTreeView',
	array('url' => array('ajaxFillTree'))
);
?>
~~~~

This will create a tree widget
[CTreeView](http://www.yiiframework.com/doc/api/1.1/CTreeView/) on your page,
and its data will be fetched using the `ajaxFillTree` of the current controller.

## The controller

So we have to add this action to the controller:

~~~~
[php] 
	/**
	 * Fills the JS tree on an AJAX request.
	 * Should receive parent node ID in $_GET['root'],
	 *  with 'source' when there is no parent.
	 */
	public function actionAjaxFillTree()
	{
		// accept only AJAX request (comment this when debugging)
		if (!Yii::app()->request->isAjaxRequest) {
			exit();
		}
		// parse the user input
		$parentId = "NULL";
		if (isset($_GET['root']) && $_GET['root'] !== 'source') {
			$parentId = (int) $_GET['root'];
		}
		$req		// read the data (this could be in a model)
		$children = Yii::app()->db->createCommand(
			"SELECT m1.id, m1.name AS text, m2.id IS NOT NULL AS hasChildren "
			. "FROM tree AS m1 LEFT JOIN tree AS m2 ON m1.id=m2.parent_id "
			. "WHERE m1.parent_id <=> $parentId "
			. "GROUP BY m1.id ORDER BY m1.name ASC"
		);
		$children = $req->queryAll();		)->queryAll();
		echo str_replace(
			'"hasChildren":"0"',
			'"hasChildren":false',
			CTreeView::saveDataAsJson($children)
		);
		exit();
	}
~~~~

When the page loads, an AJAX request will be sent to fill the first level of the
tree. It'll have the GET parameter `root` set to `source` (this is the behavior
of the [Treeview](http://bassistance.de/jquery-plugins/jquery-plugin-treeview/)
JS plugin that [CTreeView](http://www.yiiframework.com/doc/api/1.1/CTreeView/)
uses). We suppose here that the corresponding nodes (the root nodes) have a
`parent_id` set to `NULL`. In SQL, "=" can't compare a value with
`NULL`, so we have to use the `<=>` operator instead.

The other AJAX requests will have an integer value (the parent node's id) in
`$_GET['root']`. We typecast this to "int" for security.

Then the code reads the data in the database. We need a *LEFT JOIN* to find if a
node has children. If each row already has a `hasChildren` field, you can remove
this join and your SQL will be faster.

When sending the JSON-encoded result, there's a little trick: the javascript
wants the `hasChildren` attribute to be a boolean. But the SQL result just
contains 0 or 1, so we have to convert it, at least for the "false"
case. Instead of operating on the string result, one could modify the PHP array
with a `foreach`.

The `exit()` might not be necessary, but if### If AJAX fails

If your AJAX query fails, you enabled the logsshould
try to Firebug or extensions like a debug toolbar, then
Yiiit in your browser. Firefox, Chrome and Opera have good developer
toolbars that will show you the AJAX requests sent and what their answers were.
You may writefind that some JSHTML
is appended at the end of your response that would break
the expected JSON format. The drawback of `exit()` is
thatanswer. I had this problem with Yii'JS logs won't be
written, whereas `Yii::app()->end()` ends gracefully.and a toolbar
extension. In this case, insert an `exit();` at the end of the action. Or even
better: `Yii::logger->flush(); exit();`.

### Final note

This is meant as a simple introduction. There are many ways to enhance this:

* Refactor the controller action by putting its
32 last lines into a model.
* InscreaseIncrease the abstraction level, so that the
code can be used on various models, as long as they share the same table
structure.
* If the tree content rarely changes, you may consider using a different
structure, like nested sets. This will make most queries easier, e.g. fetching
the path of a node.