I made the changes in the posts.php file and found a BIG improvement in the performance of getDiscussions. Modifications marked by
// gwe modfication
if (JDEBUG)
{
JProfiler::getInstance('Application')->mark('start of modified getDiscussions ' . $option);
}
$select = "select b.*, a.`has_polls` as `polls_cnt`, a.`num_fav` as `totalFavourites`, a.`num_replies`, a.`num_attachments` as attachments_cnt,";
$select .= " a.`num_likes` as `likeCnt`, a.`sum_totalvote` as `VotedCnt`,";
$select .= " a.`replied` as `lastupdate`, a.vote as `total_vote_cnt`,";
// gwe modification
//$select .= " a.`last_user_id`, a.`last_poster_name`, a.`last_poster_email`, (select cc.anonymous from `#__discuss_posts` as cc where cc.`thread_id` = a.`id` and cc.created = a.replied limit 1) as `last_user_anonymous`,";
$select .= " a.`last_user_id`, a.`last_poster_name`, a.`last_poster_email`, ";
if ($config->get('main_anonymous_posting')){
$select .= " (select cc.anonymous from `#__discuss_posts` as cc where cc.`thread_id` = a.`id` and cc.created = a.replied limit 1) as `last_user_anonymous`,";
}
$select .= ' DATEDIFF('. $db->Quote($date->toMySQL()) . ', a.`created`) as `noofdays`, ';
// gwe
$select .= ' DATEDIFF(' . $db->Quote($date->toMySQL()) . ', a.`created`) as `daydiff`, TIMESTAMPDIFF(HOUR,' . $db->Quote($date->toMySQL()). ', a.`created`) as `timediff`,';
if ($my->id) {
$select .= " (SELECT COUNT(1) FROM " . $db->nameQuote('#__discuss_votes') . " WHERE `post_id` = a.`post_id` AND `user_id` = " . $db->Quote($my->id) . ") AS `isVoted`,";
} else {
$select .= " 0 as `isVoted`,";
}
// gwe modification
//$select .= " a.`post_status`, a.`post_type`, pt.`suffix` AS post_type_suffix, pt.`title` AS `post_type_title`,";
$select .= " a.`post_status`, a.`post_type`, ";
$select .= " e.`title` AS `category`";
$query = " from " . $db->nameQuote('#__discuss_thread') . " as a";
$query .= " inner join " . $db->nameQuote('#__discuss_posts') . " as b on a.post_id = b.id";
// Join with post types table
// gwe modification
//$query .= " LEFT JOIN " . $db->nameQuote('#__discuss_post_types') . " AS pt ON a.`post_type`= pt.`alias`";
// Join with category table.
$query .= " LEFT JOIN " . $db->nameQuote('#__discuss_category') . " AS e ON a.`category_id` = e.`id`";
if ($filter == 'favourites') {
$query .= " LEFT JOIN " . $db->nameQuote('#__discuss_favourites') . " AS f ON f.`post_id` = a.`post_id`";
}
if ($filter == 'assigned') {
$query .= " INNER JOIN " . $db->nameQuote('#__discuss_assignment_map') . " AS am ON am.`post_id` = a.post_id";
$query .= " AND am.`assignee_id` = " . $db->Quote($my->id);
}
// 3rd party integrations
if( !is_null( $reference ) && !is_null( $referenceId ) )
{
$query .= " INNER JOIN " . $db->nameQuote('#__discuss_posts_references') . " AS ref";
$query .= " ON a." . $db->nameQuote('post_id') . " = ref." . $db->nameQuote('post_id');
$query .= " AND ref." . $db->nameQuote('extension') . " = " . $db->Quote($reference);
$query .= " AND ref." . $db->nameQuote('reference_id') . " = " . $db->Quote($referenceId);
}
// conditions start here.
$where = array();
$where[] = "a.`published` = " . $db->Quote('1');
if (!ED::isSiteAdmin() && !ED::isModerator() && !$private && $filter != 'mine') {
$where[] = "a.`private` = " . $db->Quote(0);
}
if ($clusterId) {
$where[] = "a.`cluster_id` = " . $clusterId;
$includeCluster = true;
}
if (!$includeCluster) {
$where[] = "a.`cluster_id` = " . $db->Quote(0);
}
if ($user_id) {
$where[] = "a.`user_id` = " . $db->Quote((int) $user_id);
}
if (!empty($exclude)) {
$excludePost = "a.`post_id` NOT IN (";
for ($i = 0; $i < count($exclude); $i++) {
$excludePost .= $db->Quote($exclude[ $i ]);
if (next($exclude) !== false) {
$excludePost .= ",";
}
}
$excludePost .= ")";
$where[] = $excludePost;
}
if ($filteractive == 'unread') {
$viewer = ED::user();
$readPosts = $viewer->posts_read;
if ($readPosts) {
$readPosts = unserialize($readPosts);
if (count($readPosts) > 1) {
$extraSQL = implode( ',', $readPosts);
$where[] = " a.`post_id` NOT IN (" . $extraSQL . ")";
} else {
$where[] = " a.`post_id` != " . $db->Quote($readPosts[0]);
}
}
$where[] = "a.`legacy` = 0";
}
if ($filteractive == 'unanswered') {
// Should not fetch posts which are resolved
$where[] = "a.`isresolve` = " . $db->Quote(0);
$where[] = "a.`created` = a.`replied`";
}
if ($filteractive == 'favourites') {
if (empty($userId)) {
$id = $my->id;
} else {
$id = $userId;
}
$where[] = "f.`created_by` = " . $db->quote($id);
}
if ($filteractive == 'unresolved') {
$where[] = "a.`isresolve`= " . $db->Quote('0');
}
// @since 3.1 resolved filter
if ($filteractive == 'resolved') {
$where[] = "a.`isresolve`= " . $db->Quote('1');
}
if ($filter == 'answer') {
$where[] = $db->nameQuote( 'a.answered' ) . ' = ' . $db->Quote(1);
}
if ($filter == 'mine') {
$where[] = "a.`user_id` = " . $db->Quote($my->id);
}
if ($filteractive == 'unanswered') {
$where[] = "a.`answered` = " . $db->Quote('0');
}
if ($search) {
$where[] = "LOWER( a.`title` ) LIKE " . $db->Quote('%' . $search . '%');
}
// category ACL:
$catOptions = array();
$catOptions['idOnly'] = true;
if ($category) {
// $catOptions['include'] = $category;
$catOptions['includeChilds'] = $includeChilds;
} else {
$catOptions['includeChilds'] = true;
}
// $catAccessSQL = ED::category()->genCategoryAccessSQL('a.category_id', $catOptions);
// $where[] = $catAccessSQL;
$catModel = ED::model('Categories');
$catIds = $catModel->getCategoriesTree($category, $catOptions);
// if there is no categories return, means this user has no permission to view all the categories.
// if that is the case, just return empty array.
if (! $catIds) {
return array();
}
$where[] = "a.category_id IN (" . implode(',', $catIds) . ")";
if ($filteractive == 'featured' || $featured === true) {
$where[] = "a.`featured` = " . $db->Quote('1');
}
else if ($featured === false && $filter != 'resolved') {
$where[] = "a.`featured` = " . $db->Quote('0');
}
if ($filteractive == 'myposts') {
$where[] = "a.`user_id`= " . $db->Quote($my->id);
}
if ($filteractive == 'userposts' && !empty($userId)) {
$where[] = "a.`user_id`= " . $db->Quote($userId);
}
if ($filteractive == 'questions' && !empty($userId)) {
$where[] = "a.`user_id`= " . $db->Quote($userId);
}
if ($filteractive == 'new') {
$where[] = "DATEDIFF( " . $db->Quote(ED::date()->toSql()) . ", a.`created` ) <= " . $db->Quote($config->get('layout_daystostaynew'));
}
$filterLanguage = JFactory::getApplication()->getLanguageFilter();
if ($filterLanguage) {
$where[] = ED::getLanguageQuery('e.language');
}
$orderby = "";
if ($featured && $config->get('layout_featuredpost_style') != '0') {
switch ($config->get('layout_featuredpost_sort', 'date_latest')) {
case 'date_oldest':
$orderby = " ORDER BY a.`replied` ASC"; //used in getdata only
break;
case 'order_asc':
$orderby = " ORDER BY a.`ordering` ASC"; //used in getreplies only
break;
case 'order_desc':
$orderby = " ORDER BY a.`ordering` DESC"; //used in getdate and getreplies
break;
case 'date_latest':
default:
$orderby = " ORDER BY a.`replied` DESC"; //used in getsticky and get created date
break;
}
} else {
switch ($sort) {
case 'title':
$orderby = " ORDER BY a.`title` ASC"; //used in getdata only
break;
case 'popular':
$orderby = " ORDER BY `num_replies` DESC, a.`created` DESC"; //used in getdata only
break;
case 'hits':
$orderby = " ORDER BY a.`hits` DESC"; //used in getdata only
break;
case 'voted':
$orderby = " ORDER BY a.`sum_totalvote` DESC"; //used in getreplies only
break;
case 'likes':
$orderby = " ORDER BY a.`num_likes` DESC"; //used in getdate and getreplies
break;
case 'activepost':
$orderby = " ORDER BY a.`replied` DESC"; //used in getsticky and getlastreply
break;
case 'featured':
$orderby = " ORDER BY a.`featured` DESC, a.`created` DESC"; //used in getsticky and getlastreply
break;
case 'oldest':
$orderby = " ORDER BY a.`created` ASC"; //used in discussion replies
break;
case 'replylatest':
$orderby = " ORDER BY a.`created` DESC"; //used in discussion replies
break;
case 'latest':
default:
$orderby = " ORDER BY a.`replied` DESC"; //used in getsticky and get created date
break;
}
}
if (!$includeAnonymous) {
$where[] = "b.`anonymous` != " . $db->Quote(1);
}
$where = (count($where) ? " WHERE " . implode(' AND ', $where ) : "" );
$query .= $where;
// gwe mod
$order = $orderby;
$limitstart = is_null( $limitstart ) ? $this->getState( 'limitstart') : $limitstart;
$limit = is_null( $limit ) ? $this->getState( 'limit') : $limit;
$limitquery = "";
if ($limit != DISCUSS_NO_LIMIT) {
if ($pagination) {
$limitquery = " LIMIT $limitstart, $limit";
} else {
$limitquery = " LIMIT $limit";
}
}
$db->setQuery($select . $query. $order . $limitquery);
$result = $db->loadObjectList();
if ($limit != DISCUSS_NO_LIMIT && $pagination) {
// now lets get the row_count() for pagination.
// gwe modification
//$cntQuery = "select FOUND_ROWS()";
//$db->setQuery($cntQuery);
$select = "SELECT count(a.id) ";
$db->setQuery($select . $query);
$this->_total = $db->loadResult();
$this->_pagination = ED::pagination($this->_total, $limitstart, $limit);
}
// gwe modification
$typequery = "SELECT * FROM ". $db->nameQuote('#__discuss_post_types');
$db->setQuery($typequery);
$posttypes = $db->loadObjectList("alias");
foreach ($result as & $row) {
if (isset($posttypes[$row->post_type])){
$row->post_type_suffix = $posttypes[$row->post_type]->suffix;
$row->post_type_title = $posttypes[$row->post_type]->title;
}
else {
$row->post_type_suffix = "";
$row->post_type_title = "";
}
}
$this->_getDateDiffs($result);
if (JDEBUG)
{
JProfiler::getInstance('Application')->mark('end of modified getDiscussions ' . $option);
}