Yii 1.1: Display an AJAX tree from your DB using CTreeView

20 followers

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).

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
$this->widget(
    'CTreeView',
    array('url' => array('ajaxFillTree'))
);
?>

This will create a tree widget 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:

/**
     * 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'];
        }
        // 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"
        )->queryAll();
        echo str_replace(
            '"hasChildren":"0"',
            '"hasChildren":false',
            CTreeView::saveDataAsJson($children)
        );
    }

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 JS plugin that 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.

If AJAX fails

If your AJAX query fails, you should try to debug it 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 find that some HTML is appended at the end of the expected JSON answer. I had this problem with Yii'JS logs 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 2 last lines into a model.
  • Increase 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.

Total 9 comments

#8661 report it
François Gannaz at 2012/06/18 03:35am
Non AJAX trees

@Amjad Khan
You should use the forum to ask such questions. A few tips:

  • This tutorial uses AJAX to display the child nodes, it's not meant for displaying an expanded tree by default. You should use the data property of CTreeView.
  • If your tree is very small, you could use SQL queries in a loop to fetch nodes.
  • If your tree is large, you have to put more metadata in your structure. Either opt for nested sets, or add a "path" column which you can sort on.
#8650 report it
Amjad Khan at 2012/06/16 06:18pm
yii treeview collasped issue

Sir please give me a tip that how can i get the expanded treeview on the first load of the page and not collapsed, i changed the collapsed property to false and true but it has no effect.

#8318 report it
François Gannaz at 2012/05/25 07:03am
Answers

@Backslider
This is a wiki, you know. Instead of useless comments, you could enhance this article. I'm not sure splitting this fat controller into a controller and a model would make the article easier to follow. This is meant as a simple how-to, so I'd rather keep this light introduction and suggest at the end to refactor the 3 last lines of the action into a model.

@rumaddict
Nested sets have a different use case. They make insertion difficult, so one shouldn't use them on changing data. Their concept is also bit harder, so I prefer to use this simple structure in this small Yii tutorial.

#8315 report it
Backslider at 2012/05/25 02:21am
Business logic in controller

Why do you have business logic in your controller?

Most of what I see in your example belongs in a model.... MVC, you know?

#4382 report it
drumaddict at 2011/07/02 06:07pm
Why not use nested set

For hierarchical structured data I think the best solution is to use the nested set model. There are several extensions for Yii,for example: http://www.yiiframework.com/extension/nestedsetbehavior

#3784 report it
macinville at 2011/05/08 04:36am
Displaying image and links

I wanted to use this feature as a side navigation menu, so it should be able to create links in it.

Here I have posted my working solution (I just did some minor modifications with the controller).

Hope I was able to help someone.

#3608 report it
Nacesprin at 2011/04/21 07:14pm
m1 and m2

As you can see, m1 and m2 are the same table "left joined"

#3606 report it
nettrinity at 2011/04/21 04:39pm
what is m1 and m2 here?

"SELECT m1.id, m1.name AS text, m2.id IS NOT NULL AS hasChildren "

not quite understand m1 and m2 here. Can you help me explain?

#3376 report it
Nacesprin at 2011/04/07 11:31am
Handling onclick action

In order to be able to get the ID of the record, you can achieve changing a bit the TEXT selection field as follow:

..."SELECT m1.id, CONCAT('<span onclick=\"alert(\'',m1.id,'\')\">',m1.name,'</span>') AS text, m2.id IS NOT NULL AS hasChildren "....

Leave a comment

Please to leave your comment.

Write new article