SilverStripe: Replace getSiteTreeFor with single query

From FVue
Jump to: navigation, search

Problem

Building up the sitetree for a Silverstripe environment is slow because a database query is done for every tree node. And then only the current level is retrieved: expanding a branch will make you wait again.

Environment

  • SilverStripe-2.4.7
  • PostgreSQL-8.4

Solution

Replace getSiteTreeFor() with a single query. This gives a 30% speed gain on loading the site tree.

NOTE: this only works for PostgreSQL >= 8.4

You can also check out my project on github:

diff --git a/code/LeftAndMain.php b/code/LeftAndMain.php
index b3c81eb..bc19702 100644
--- a/code/LeftAndMain.php
+++ b/code/LeftAndMain.php
@@ -498,6 +498,153 @@ class LeftAndMain extends Controller {
 	}
 
 	/**
+	 * Recursive function
+	 */
+	function chiGetSiteTreeForRecursive(&$tree, $parent_id = 0) {
+        $html = $prev = $next = null;
+        $added_to_draft = _t('SiteTree.ADDEDTODRAFT', 'Added to draft site');
+        $modified_on_draft = _t('SiteTree.MODIFIEDONDRAFT', 'Modified on draft site');
+        $removed_from_draft = _t('SiteTree.REMOVEDFROMDRAFT', 'Removed from draft site');
+        $page_type = _t('LeftAndMain.PAGETYPE','Page type: ');
+        while (! is_null($node = array_shift($tree))) {
+            $id = $node['ID'];
+            $class = $node['ClassName'];
+            $css = array($class, 'closed');
+            $node['ShowInMenus'] || $css[] = 'notinmenu';
+            $node['ID'] == $this->currentPageID() && $css[] = 'current';
+            $csss = join(' ', $css);
+            $title = $node['Title'];
+            if ($node['DraftOnly'] == 't') {
+                $title = "<ins title=\"$added_to_draft\">$title</del>";
+            } else {
+                if ($node['Status'] == 'Saved (update)') {
+                    $title = "<span class='modified' title=\"$modified_on_draft\">$title</span>";
+                }
+                if ($node['LifeOnly'] == 't') {
+                    $title = "<del title=\"$removed_from_draft\">$title</del>";
+                }
+            }
+            if ($node['ParentID'] == $parent_id && is_null($node['DraftOnly'])) {
+                $html .= <<<HTML
+                <li id="record-$id" class="$csss">
+                    <a href="admin/show/$id" class="$csss" title="$page_type$class">$title</a>
+HTML;
+                // Is there a next node?
+                if (count($tree)) {
+                    // Yes, there's a next node
+                    // Is next node a child of this node?
+                    if ($tree[0]['ParentID'] == $node['ID']) {
+                        // Yes, next node is a child of this node;
+                        // Insert child(ren) recursive for this node
+                        $html .= "\n<ul>\n" . $this->chiGetSiteTreeForRecursive($tree, $node['ID']) . "\n</ul>\n";
+                    } else {
+                        // No, next node isn't a child of this node;
+                        // Close html
+                        $html .= '</li>';
+                        // Is next node a child of this recursive-parent?
+                        if ($tree[0]['ParentID'] != $parent_id && ! is_null($node['DraftOnly'])) {
+                            // No, next node isn't a child of this recursive-parent;
+                            // End this recursion
+                            break;
+                        }
+                    }
+                } else {
+                    // No, there is no next node;
+                    // Close html
+                    $html .= '</li>';
+                }
+            } else {
+                if ($parent_id != 0) {
+                    array_unshift($tree, $node);
+                    break;
+                } else {
+                    $html .= <<<HTML
+                    <li id="record-$id" class="$csss">
+                        <a href="admin/show/$id" class="$csss" title="$page_type$class">$title</a>
+                    </li>
+HTML;
+                }
+            }
+        }
+        return $html;
+	}
+
+	function chiGetSiteTreeFor() {
+        # See: http://explainextended.com/2009/07/17/postgresql-8-4-preserving-order-for-hierarchical-query/
+        # NOTE: Tree items existing in SiteTree only (not SiteTree_Live) - i.e.
+        #       with title "Added to draft site" - are shown at the bottom of the site
+        #       tree.  This is because the recursive query below doesn't accept a FULL JOIN of t2.
+        $query =<<<SQL
+        WITH RECURSIVE q AS (
+            SELECT l, 1                                AS level, 
+                ARRAY[COALESCE(t."Sort", l."Sort"), COALESCE(t."ID", l."ID")] AS breadcrumb,   
+                COALESCE(t."Status", l."Status")       AS "Status", 
+                t."ID" IS NULL                         AS "LifeOnly",
+                COALESCE(t."Title", l."Title")         AS "Title",
+                COALESCE(t."MenuTitle", l."MenuTitle") AS "MenuTitle"
+            FROM "SiteTree_Live" l
+                LEFT JOIN "SiteTree" t
+                    ON t."ID" = l."ID"
+            WHERE COALESCE(t."ParentID", l."ParentID") = 0
+            UNION ALL
+            SELECT l2, q.level + 1                       AS level, 
+                breadcrumb || ARRAY[COALESCE(t2."Sort", l2."Sort"), 
+                COALESCE(t2."ID", l2."ID")]              AS "ID", 
+                COALESCE(t2."Status", l2."Status")       AS "Status", 
+                t2."ID" IS NULL                          AS "LifeOnly",
+                COALESCE(t2."Title", l2."Title")         AS "Title",
+                COALESCE(t2."MenuTitle", l2."MenuTitle") AS "MenuTitle"
+            FROM q
+                INNER JOIN "SiteTree_Live" l2
+                    ON l2."ParentID" = (q.l)."ID"
+                LEFT JOIN "SiteTree" t2
+                    ON t2."ID" = l2."ID"
+        )
+        SELECT COALESCE((q.l)."ID", f."ID") AS "ID",
+            COALESCE((q.l)."ParentID", f."ParentID") AS "ParentID",
+            COALESCE(q."MenuTitle", q."Title", f."MenuTitle", f."Title") AS "Title",
+            COALESCE((q.l)."ClassName", f."ClassName") AS "ClassName",
+            COALESCE((q.l)."ShowInMenus", f."ShowInMenus") AS "ShowInMenus",
+            COALESCE(q."Status", f."Status") AS "Status",
+            "LifeOnly",
+            "DraftOnly"
+        FROM    q
+            FULL JOIN (
+                SELECT "ID", "ParentID", "MenuTitle", "Title", "ClassName", "ShowInMenus", "Status", 'f', 't' AS "DraftOnly"
+                FROM "SiteTree" 
+                    WHERE "ID" NOT IN (
+                        SELECT "ID" FROM "SiteTree_Live"
+                    )
+            ) f ON f."ID" = (q.l)."ID"
+        ORDER BY breadcrumb;
+SQL;
+        $vw = DB::query($query);
+        $tree = pg_fetch_all(DB::$lastQuery);
+
+        // This lets us override the tree title with an extension
+        if($this->hasMethod('getCMSTreeTitle') && $customTreeTitle = $this->getCMSTreeTitle()) {
+            $treeTitle = $customTreeTitle;
+        } else {
+            $siteConfig = SiteConfig::current_site_config();
+            $treeTitle =  $siteConfig->Title;
+        }
+        $rootLink = $this->Link('show') . '/root';
+        $html = $this->chiGetSiteTreeForRecursive($tree);
+        return <<<HTML
+        <div id="sitetree_ul">
+            <ul id="sitetree" class="tree unformatted">
+              <li id="record-0" class="Root nodelete">
+                <a href="$rootLink"><strong>$treeTitle</strong></a>
+                <ul>
+                  $html
+                </ul>
+              </li>
+            </ul>
+        </div>
+HTML;
+    }
+
+	/**
 	 * Get a site tree displaying the nodes under the given objects
 	 * @param $className The class of the root object
 	 * @param $rootID The ID of the root object.  If this is null then a complete tree will be
@@ -506,6 +653,7 @@ class LeftAndMain extends Controller {
 	 *                        Children, AllChildrenIncludingDeleted, or AllHistoricalChildren
 	 */
 	function getSiteTreeFor($className, $rootID = null, $childrenMethod = null, $numChildrenMethod = null, $filterFunction = null, $minNodeCount = 30) {
+		return $this->chiGetSiteTreeFor();
 		// Default childrenMethod and numChildrenMethod
 		if (!$childrenMethod) $childrenMethod = 'AllChildrenIncludingDeleted';
 		if (!$numChildrenMethod) $numChildrenMethod = 'numChildren';