/**
* 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();
}
}
