/** * Fetches the SQL for loading. * $required_query is used to identify which query to build for classes that * have multiple queries for fetching info. * * This can safely be based on $this->required_info as long as a consitent * flag is used for identifying the query. * * @param int $required_query - The required query * @param bool $force_rebuild - Whether to rebuild the string * * @return string */ protected function getLoadQuery($required_query = self::QUERY_BASIC, $force_rebuild = false) { // Hooks should check the required query before populating the hook vars $hook_query_fields = $hook_query_joins = $hook_query_where = ''; ($hook = vBulletinHook::fetch_hook($this->query_hook)) ? eval($hook) : false; if (self::QUERY_BASIC == $required_query) { if (! isset(vB::$vbulletin->userinfo['permissions']['cms'])) { vBCMS_Permissions::getUserPerms(); } $extrasql = '' ; $permissionstring = "( (" . vBCMS_Permissions::getPermissionString() . ($this->filter_includepreview ? ") OR (node.setpublish AND node.publishdate <" . TIMENOW . " AND node.publicpreview > 0" : '' ) . "))" ; if (!$this->sortby) { if ($this->orderby == 3) { $extrasql = " INNER JOIN (SELECT parentnode, MAX(lastupdated) AS lastupdated FROM " . TABLE_PREFIX . "cms_node AS node WHERE contenttypeid <> " . vb_Types::instance()->getContentTypeID("vBCms_Section") . " AND " . vBCMS_Permissions::getPermissionString() . " GROUP BY parentnode ) AS ordering ON ordering.parentnode = node.parentnode AND node.lastupdated = ordering.lastupdated WHERE 1=1"; $this->sortby = " ORDER BY node.setpublish DESC, node.publishdate DESC "; } else if ($this->orderby == 2) { $this->sortby = " ORDER BY node.publishdate DESC "; } else if ($this->orderby == 4) { $this->sortby = " ORDER BY info.title ASC "; } else if ($this->orderby == 5) { $this->sortby = " ORDER BY sectionorder.displayorder ASC "; } else { $this->sortby = " ORDER BY CASE WHEN sectionorder.displayorder > 0 THEN sectionorder.displayorder ELSE 9999999 END ASC, node.publishdate DESC"; } } //We need a nodeid for the displayorder below if ($this->filter_node_exact AND !$this->filter_node ) { $this->filter_node = $this->filter_node_exact; } //enforce the max_records limits if ($this->max_records) { $this->paginate = true; if (!$this->start) { $this->start = 0; } $this->quantity = $this->max_records; } $sql = "SELECT " . ($this->count_records ? 'SQL_CALC_FOUND_ROWS': '') . " node.nodeid AS itemid, (node.nodeleft = 1) AS isroot, node.nodeid, node.contenttypeid, node.contentid, node.url, node.parentnode, node.styleid, node.userid, node.layoutid, node.publishdate, node.setpublish, node.issection, parent.permissionsfrom as parentpermissions, node.permissionsfrom, node.publicpreview, node.showtitle, node.showuser, node.showpreviewonly, node.showall, node.showupdated, node.showviewcount, node.showpublishdate, node.settingsforboth, node.includechildren, node.editshowchildren, node.shownav, node.hidden, node.nosearch, node.nodeleft, info.description, info.title, info.html_title, info.viewcount, info.creationdate, info.workflowdate, info.workflowstatus, info.workflowcheckedout, info.workflowlevelid, info.associatedthreadid, user.username, sectionorder.displayorder, thread.replycount, parentinfo.title AS parenttitle $hook_query_fields FROM " . TABLE_PREFIX . "cms_node AS node INNER JOIN " . TABLE_PREFIX . "cms_nodeinfo AS info ON info.nodeid = node.nodeid $hook_query_join LEFT JOIN " . TABLE_PREFIX . "user AS user ON user.userid = node.userid LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON thread.threadid = info.associatedthreadid LEFT JOIN " . TABLE_PREFIX . "cms_sectionorder AS sectionorder ON sectionorder.sectionid = " . intval($this->filter_node) ." AND sectionorder.nodeid = node.nodeid LEFT JOIN " . TABLE_PREFIX . "cms_node AS parent ON parent.nodeid = node.parentnode LEFT JOIN " . TABLE_PREFIX . "cms_nodeinfo AS parentinfo ON parentinfo.nodeid = parent.nodeid " . (intval($this->filter_node) ? " INNER JOIN " . TABLE_PREFIX . "cms_node AS rootnode ON rootnode.nodeid = " . intval($this->filter_node) . " AND (node.nodeleft >= rootnode.nodeleft AND node.nodeleft <= rootnode.noderight) AND node.nodeleft != rootnode.nodeleft " : '') . $extrasql . " AND node.contenttypeid <> " . vb_Types::instance()->getContentTypeID("vBCms_Section") . " AND node.new != 1 " . ($this->itemid ? "AND node.nodeid IN (" . implode(',', $this->itemid) . ") " : '') . ($this->filter_contenttype ? "AND node.contenttypeid = " . intval($this->filter_contenttype) . " " : '') . ($this->filter_contentid ? "AND node.contentid = " . intval($this->contentid) . " ": '') . ($this->filter_nosections ? "AND node.issection != '1' " : '') . ($this->filter_onlysections ? "AND node.issection = '1' " : '') . ($this->filter_ignorepermissions ? '' : " AND " . $permissionstring) . ($this->filter_userid ? "AND node.userid = " . intval($this->filter_userid) . " " : '') . ($this->visible_only ? "AND node.hidden = 0 " : '') . ($this->filter_published ? "AND node.setpublish = '1' AND node.publishdate <= " . intval(TIMENOW) . " " : '') . ($this->filter_unpublished ? "AND node.setpublish = '0' OR node.publishdate > " . intval(TIMENOW) . " " : '') . " " . $this->getFilterNotContentTypeSql() . (intval($this->filter_node_exact) ? "AND (node.parentnode = " . $this->filter_node_exact . " OR sectionorder.displayorder > 0 )": '') . (($this->orderby == 5) ? " AND sectionorder.displayorder > 0 " : '') . " $content_query_where $hook_query_where " . $this->sortby . ( ($this->paginate AND intval($this->quantity)) ? (" LIMIT " . intval($this->start) . ', ' . intval($this->quantity)) : '') ; return $sql; } else { return parent::getLoadQuery(); } }