SilverStripe: Replace getSiteTreeFor with single query
From FVue
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';
