/**
* Возвращает список тем в разделе
*
* -------------------------------------------------------------------------------------
* Внимание! Замечание к подзапросу на основные вкладки (новые, популярные, актуальные).
*
* Подзапрос настроен на работу индексов типа "ix blogs_themes/viewgroup*", поэтому любое
* добавление условий или (тем более с привязкой дополнительных таблиц!) испортит его.
* Суть заключается в том, что мы все условия копируем в таблицы blogs_themes из таблиц blogs_msgs и других,
* для того, чтобы использовать индексы и не делать лишних джойнов, т.е. все проверки делаются в рамках
* единственной
* таблицы. Поэтому мы быстро выбираем все необходимые N тем и только потом связываем их с остальными таблицами.
* Чтобы добавить условие, нужно по аналогии, например, с deleted, is_blocked и т.п.), добавить клон
* необходимого
* поля в blogs_themes и скорректировать индекс(ы) (протестировав предварительно).
* -------------------------------------------------------------------------------------
*
*
* @param integer $gr_num идентификатор раздела
* @param string $gr_name возвращает название раздела
* @param integer $num_treads возвращает кол-во тем в данном разделе
* @param integer $page номер страницы из списка тем, которую надо выводить [получает или возвращает, в зависимости от $having_message]
* @param string $error возвращает сообщение об ошибке
* @param integer $fid UID текущего юзера
* @param integer $mod имеет ли текущий юзер права на просмотр данного раздела [1 - да, 0 - нет]
* @param integer $having_message УСТАРЕЛО. id сообщения, страницу с которым необходимо отобразить (0 - показать страницу $page)
* @param integer $read_only возвращает параметр - является ли данный раздел разделом "только для чтения"
* @param string $ord параметр сортировки ("my", "relevant", "best", "new", "favs")
* @param string $sort_order позволяет передать дополнительные условия сортировки
* @return array [[информация о сообщении]]
*/
function GetGroup($gr_num, &$gr_name, &$num_treads, $page, &$error, $fid = 0, $mod = 1, $having_message = 0, &$read_only, $ord = "new", $is_ban = true, $sort_order = '')
{
global $DB;
$cachedelay = 300;
if (hasPermissions("blogs")) {
$cachedelay = 0;
}
$memBuff = new memBuff();
$fid = (int) $fid;
$gr_num = (int) $gr_num;
$num_treads = 0;
$year = date('Y');
$can_prevyear_ontops = date('n') <= 2;
$limit = $GLOBALS['blogspp'];
$group = $gr_num != 0 ? "id_gr = {$gr_num} " : "";
$offset = $limit * ($page - 1);
$offset = intvalPgSql((string) $offset);
$limit_str = "LIMIT {$limit} OFFSET {$offset}";
$order = "post_time DESC";
$ids = array();
$idx = array();
$get_ontops = $ord == 'ontop';
if (!$get_ontops) {
if ($gr_num != 0) {
$gr_name = $this->GetGroupName($gr_num, 0, $mod);
if (!$gr_name) {
$error = "У вас недостаточно прав для просмотра этого форума";
return 0;
}
} else {
$gr_name = "Все вместе";
}
}
// смотрим баны
if ($fid && $fid == $_SESSION['uid']) {
$role = $_SESSION['role'];
} else {
if ($fid) {
$users = new users();
$role = $users->GetField($fid, $error, 'role');
} else {
$role = 0;
}
}
$is_moder = hasPermissions('blogs', $fid);
if ($is_moder) {
$sel_blocked = ", moderators.login as moder_login, moderators.uname AS moder_name, moderators.usurname as moder_uname";
$join_blocked = " LEFT JOIN users AS moderators ON blogs_blocked.admin = moderators.uid ";
} else {
$where_blocked = '(t.is_blocked = false) ';
$where_private = ' AND (' . ($fid ? "t.fromuser_id = {$fid} OR " : '') . 't.is_private = false)';
$where_deleted = ' AND (t.deleted IS NULL )';
$where_deleted_my = ' AND t.deleted IS NULL AND m.deleted IS NULL';
// контролим баны
if (!$is_ban) {
$where_is_blocked = $where_blocked;
}
}
$_group = $group ? " {$group} AND " : '';
switch ($ord) {
case "my_all":
case "my_posts":
case "my_comments":
case 'favs_list':
case 'favs_std':
if ($ord != 'favs_list' && $ord != 'favs_std') {
$sReplyTo = $ord != 'my_all' ? $ord == 'my_posts' ? ' AND m.reply_to IS NULL' : ' AND m.reply_to IS NOT NULL' : '';
$join_banned = $where_deleted ? 'INNER JOIN users mtu
ON mtu.uid = t.fromuser_id AND mtu.is_banned = 0::bit(1)' : '';
$where = 'WHERE';
$and = ' AND ';
/*if (!$where_blocked && !$_group && !$where_private && !$sReplyTo && !$where_deleted_my) {
$and = $where = '';
}*/
if (!$where_blocked) {
$and = '';
}
$sSelectQuery = "SELECT\n ontop, users.warn, msgs.id, messages_cnt as num, post_time, msgtext, yt_link, msgs.title, close_comments, is_private, users.uid,\n base as t, msgs.id_gr, users.uname, modified, modified_id, fromuser_id, users.usurname, users.login, users.email, users.photo, users.is_team,\n users.is_pro as payed, users.is_pro_test as payed_test, users.role, users.is_banned, users.ban_where, msgs.thread_id, users.reg_date, users.is_chuck,\n blogs_blocked.thread_id as is_blocked, blogs_blocked.reason, blogs_blocked.blocked_time,\n blogs_poll.thread_id::boolean as has_poll, blogs_poll.question as poll_question, blogs_poll.closed as poll_closed, blogs_poll.multiple as poll_multiple,\n\t\t\t\t\t\tvotes._cnt as poll_votes, reply_to, msgs.deleted, msgs.deluser_id, msgs.deleted_reason, fav_cnt, w.status, moderator_status, sbr_meta.completed_cnt\n FROM (\n SELECT m.*, t.messages_cnt, t.base, t.id_gr, t.close_comments , t.is_private, t.fav_cnt \n FROM blogs_themes as t\n INNER JOIN blogs_msgs as m ON t.thread_id = m.thread_id\n {$join_deleted}\n {$join_banned}\n {$where} {$where_blocked} {$and} ({$_group} m.fromuser_id = '{$fid}' {$where_private} {$sReplyTo}) {$where_deleted_my}\n ORDER BY {$order} {$limit_str}\n ) as msgs\n LEFT JOIN users ON fromuser_id=uid\n LEFT JOIN sbr_meta ON sbr_meta.user_id=uid\n LEFT JOIN blogs_blocked ON msgs.thread_id = blogs_blocked.thread_id\n LEFT JOIN blogs_poll ON blogs_poll.thread_id = msgs.thread_id\n\t\t\t\t\tLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = '{$fid}' GROUP BY thread_id) AS votes ON votes.thread_id = msgs.thread_id\n LEFT JOIN blogs_themes_watch w ON w.user_id = '{$fid}' AND w.theme_id = msgs.thread_id\n ORDER BY {$order}";
$sCountQuery = "\n SELECT COUNT(*) as num\n FROM blogs_msgs m \n LEFT JOIN blogs_themes t ON t.thread_id = m.thread_id\n {$join_deleted}\n \t{$join_banned}\n {$where} {$where_blocked} {$and} ({$_group} m.fromuser_id = ?i {$where_private} {$sReplyTo}) {$where_deleted_my}";
} else {
if ($sort_order == "priority") {
$order = " priority DESC";
// важности
} elseif ($sort_order == "abc") {
$order = " calc_title";
// алфавиту
} else {
$order = " add_time DESC NULLS LAST";
// дате
}
$where = 'WHERE';
$and = ' AND ';
/*if (!$where_blocked && !$_group && !$where_private && !$where_deleted) {
$and = $where = '';
}*/
if (!$where_blocked) {
$and = '';
}
$sSelectQuery = "SELECT \n ontop, users.warn, msgs.id, messages_cnt as num, post_time, msgtext, yt_link, msgs.calc_title, close_comments, \n is_private, users.uid, base as t, msgs.id_gr, users.uname, modified, modified_id, users.is_team,\n fromuser_id, users.usurname, users.login, users.email, users.photo, users.is_pro as payed, reply_to, \n users.is_pro_test as payed_test, users.role, users.is_banned, users.ban_where, msgs.thread_id, users.reg_date, \n users.is_chuck, blogs_blocked.thread_id as is_blocked, blogs_blocked.reason, blogs_blocked.blocked_time, \n blogs_poll.thread_id::boolean as has_poll, blogs_poll.question as poll_question, blogs_poll.closed as poll_closed, blogs_poll.multiple as poll_multiple,\n votes._cnt as poll_votes, msgs.priority, msgs.add_time, msgs.deleted, msgs.deluser_id, msgs.deleted_reason, fav_cnt, w.status, moderator_status, sbr_meta.completed_cnt\n FROM ( \n SELECT m.*, COALESCE(NULLIF(f.title,''), m.title) as calc_title, t.messages_cnt, t.base, t.id_gr, t.close_comments , t.is_private, f.priority, f.add_time, t.fav_cnt \n FROM blogs_fav f \n INNER JOIN blogs_themes as t ON f.thread_id = t.thread_id \n INNER JOIN blogs_msgs as m ON t.thread_id = m.thread_id \n {$where} {$where_blocked} {$and} ({$_group} m.reply_to IS NULL AND f.user_id = '{$fid}' {$where_private}) {$where_deleted} \n ORDER BY {$order} " . ($ord == 'favs_std' ? $limit_str : '') . " \n ) as msgs \n LEFT JOIN users ON fromuser_id=uid \n LEFT JOIN blogs_blocked ON msgs.thread_id = blogs_blocked.thread_id \n LEFT JOIN blogs_poll ON blogs_poll.thread_id = msgs.thread_id\n LEFT JOIN sbr_meta ON sbr_meta.user_id=users.uid\n\t\t\t\t\tLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = '{$fid}' GROUP BY thread_id) AS votes ON votes.thread_id = msgs.thread_id\n\t\t\t\t\tLEFT JOIN blogs_themes_watch w ON w.user_id = '{$fid}' AND w.theme_id = msgs.thread_id\n ORDER BY {$order}";
$sCountQuery = "\n SELECT COUNT(*) as num \n FROM blogs_fav f \n INNER JOIN blogs_themes as t ON f.thread_id = t.thread_id \n INNER JOIN blogs_msgs as m ON t.thread_id = m.thread_id \n {$where} {$where_blocked} {$and} ({$_group} m.reply_to IS NULL AND f.user_id = ?i {$where_private}) {$where_deleted}";
}
$ret = $DB->rows($sSelectQuery);
$error = $DB->error;
if ($error) {
$error = parse_db_error($error);
} else {
for ($i = 0, $max = count($ret); $i < $max; $i++) {
if ($ret[$i]['has_poll'] == 't') {
$ids[] = $ret[$i]['thread_id'];
$idx[$ret[$i]['thread_id']] =& $ret[$i];
}
}
$this->AddAttach($ret);
$num_treads = $DB->val($sCountQuery, $fid);
}
if ($ids) {
$res = $DB->rows("SELECT * FROM blogs_poll_answers WHERE thread_id IN (" . implode(',', $ids) . ") ORDER BY id");
if ($res) {
foreach ($res as $row) {
$idx[$row['thread_id']]['poll'][] = $row;
}
}
}
return $ret;
break;
case "new":
case "relevant":
case "best":
case "ontop":
default:
$group = $group ? $group : "id_gr!=7 ";
$_group = $group ? " AND {$group}" : '';
if ($ord == 'relevant') {
$order = "last_activity DESC";
} else {
if ($ord == 'best') {
$order = "messages_cnt DESC," . $order;
} else {
if (!$get_ontops) {
$y_start = $year;
$ycnt = 0;
$ylcnt = 0;
if (!$fid) {
for ($year, $ycnt = 0; $year >= 2008; $year--) {
$ylcnt = blogs::getThemesCount($year, $gr_num);
$ycnt += $ylcnt;
if ($ycnt > $offset) {
break;
}
}
}
$ontops = $this->GetGroup($gr_num, $gr_name, $num_treads, 1, $error, $fid, $mod, 0, $read_only, 'ontop', $is_ban, $sort_order);
$ontops_cnt = $ontops ? count($ontops) : 0;
if ($offset >= $ontops_cnt) {
$offset -= $ontops_cnt;
}
$where_ontop_i = 'AND t.ontop = false';
if (!$group && !$where_is_blocked && !$where_private && !$where_deleted) {
$where_ontop_i = ' t.ontop = false';
}
if ($year >= 2008) {
$y_offset = $offset - ($ycnt - $ylcnt);
$y_offset = intvalPgSql((string) $y_offset);
$limit_str = "LIMIT {$limit} OFFSET {$y_offset}";
} else {
$sql = NULL;
// прыгаем на общий запрос.
break;
}
} else {
// берем только закрепленные.
$look_prev_year = $can_prevyear_ontops;
$where_ontop_i = 'AND t.ontop = true';
if (!$group && !$where_is_blocked && !$where_private && !$where_deleted) {
$where_ontop_i = ' t.ontop = true';
}
}
}
}
$where = 'WHERE';
if (!$where_blocked && !$group && !$where_private && !$where_deleted && !$where_ontop_i) {
$where = '';
}
if (!$where_is_blocked) {
$_group = $group;
}
$sql = "\n SELECT ontop, users.warn, msgs.id, messages_cnt as num, last_activity, post_time, msgtext, yt_link, msgs.title, is_private, close_comments, users.uid,\n base as t, msgs.id_gr, users.uname, modified, modified_id, fromuser_id, users.usurname, users.login, users.email, users.photo, users.is_team,\n users.is_pro as payed, users.is_pro_test as payed_test, users.role, users.is_banned, users.ban_where, msgs.thread_id, users.reg_date, users.is_chuck,\n blogs_blocked.thread_id as is_blocked, blogs_blocked.reason, blogs_blocked.blocked_time, msgs.deleted_reason,\n\t\t\t\t\t\t blogs_poll.thread_id::boolean as has_poll, blogs_poll.question as poll_question, blogs_poll.closed as poll_closed, blogs_poll.multiple as poll_multiple,\n\t\t\t\t\t\t votes._cnt as poll_votes, msgs.deleted, msgs.deluser_id, fav_cnt, w.status, sbr_meta.completed_cnt, moderator_status {$sel_blocked}\n FROM (\n -- Внимание! Данный подзапрос заточен под индексы blogs_themes (см. замечание в описании функции).\n SELECT m.*, t.close_comments, t.is_private, t.messages_cnt, t.last_activity, t.base, t.id_gr, t.fav_cnt \n FROM (\n SELECT t.* FROM blogs_themes as t\n {$where} /*where_is_blocked*/{$where_is_blocked} /*group:*/{$_group} /*where_private*/{$where_private} /*where_deleted:*/{$where_deleted} /*where_ontop_i*/{$where_ontop_i}\n ORDER BY {$order} {$limit_str}\n ) as t\n INNER JOIN\n blogs_msgs" . (!$look_prev_year ? "_{$year}" : '') . " m\n ON t.thread_id = m.thread_id AND m.reply_to IS NULL\n ) as msgs\n\t\t\t\t\tLEFT JOIN users ON fromuser_id=users.uid\n\t\t\t\t\tLEFT JOIN sbr_meta ON sbr_meta.user_id=users.uid\n\t\t\t\t\tLEFT JOIN blogs_blocked ON msgs.thread_id = blogs_blocked.thread_id\n\t\t\t\t\tLEFT JOIN blogs_poll ON blogs_poll.thread_id = msgs.thread_id\n\t\t\t\t\tLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = '{$fid}' GROUP BY thread_id) AS votes ON votes.thread_id = msgs.thread_id\n\t\t\t\t\tLEFT JOIN blogs_themes_watch" . (!$look_prev_year ? "_{$year}" : '') . " w ON w.user_id = '{$fid}' AND w.theme_id = msgs.thread_id\n {$join_blocked}\n ORDER BY {$order}\n\t\t\t\t";
$ret = $DB->rows($sql);
$error = $DB->error;
if ($error || (!$ret || count($ret) < $limit) && !$get_ontops) {
$sql = '';
}
break;
}
if (!$sql) {
$offset = intvalPgSql((string) $offset);
$where = 'WHERE';
if (!$where_blocked && !$group && !$where_private && !$where_deleted && !$where_ontop_i) {
$where = '';
}
$sql = "\n SELECT ontop, users.warn, msgs.id, messages_cnt as num, last_activity, post_time, msgtext, yt_link, msgs.title, is_private, close_comments, users.uid,\n base as t, msgs.id_gr, users.uname, modified, modified_id, fromuser_id, users.usurname, users.login, users.email, users.photo, users.is_team,\n users.is_pro as payed, users.is_pro_test as payed_test, users.role, users.is_banned, users.ban_where, msgs.thread_id, users.reg_date, users.is_chuck,\n blogs_blocked.thread_id as is_blocked, blogs_blocked.reason, blogs_blocked.blocked_time,\n\t\t\t\t\t blogs_poll.thread_id::boolean as has_poll, blogs_poll.question as poll_question, blogs_poll.closed as poll_closed, blogs_poll.multiple as poll_multiple,\n\t\t\t\t\t votes._cnt as poll_votes, msgs.deleted, msgs.deluser_id, msgs.deleted_reason, sbr_meta.completed_cnt, fav_cnt, w.status, moderator_status {$sel_blocked}\n FROM (\n SELECT m.*, t.close_comments, t.is_private, t.messages_cnt, t.last_activity, t.base, t.id_gr, t.fav_cnt \n FROM (\n -- Внимание! Данный подзапрос заточен под индексы blogs_themes (см. замечание в описании функции).\n SELECT t.* FROM blogs_themes as t\n {$where} {$where_is_blocked} {$_group} {$where_private} {$where_deleted} {$where_ontop_i}\n ORDER BY {$order}\n LIMIT {$limit} OFFSET {$offset}\n ) as t\n INNER JOIN blogs_msgs as m ON t.thread_id = m.thread_id AND m.reply_to IS NULL\n ) as msgs\n LEFT JOIN users ON fromuser_id=uid\n LEFT JOIN blogs_blocked ON msgs.thread_id = blogs_blocked.thread_id\n\t\t\t LEFT JOIN blogs_poll ON blogs_poll.thread_id = msgs.thread_id\n\t\t\t LEFT JOIN sbr_meta ON sbr_meta.user_id = users.uid\n\t \t LEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = '{$fid}' GROUP BY thread_id) AS votes ON votes.thread_id = msgs.thread_id\n LEFT JOIN blogs_themes_watch w ON w.user_id = '{$fid}' AND w.theme_id = msgs.thread_id\n {$join_blocked}\n ORDER BY {$order}\n ";
$ret = $DB->rows($sql);
$error = $DB->error;
if ($error) {
$error = parse_db_error($error);
}
}
if (!$error && $ret && !$num_treads) {
for ($i = 0, $max = count($ret); $i < $max; $i++) {
if (!$is_moder && $ret[$i]['is_blocked'] && $fid && $ret[$i]['fromuser_id'] != $fid) {
unset($ret[$i]);
}
if ($ret[$i]['has_poll'] == 't') {
$ids[] = $ret[$i]['thread_id'];
$idx[$ret[$i]['thread_id']] =& $ret[$i];
}
}
if ($ret) {
$ret = array_values($ret);
} else {
$ret = array();
}
$this->AddAttach($ret);
if (!$get_ontops) {
// Из $where_private специально убрана проверка по $fid!
// Иначе оно слишком затратно, т.к. не дает кэшировать этот запрос для всех юзеров сразу
// при его довольно малой значимости.
// То же с $where_is_blocked (убираем проверку fromuser_id).
// Если в связи с этим будут выписаны баги в мантисе и т.п., нужно обсудить, прежде чем втыкать его обратно.
if ($where_private) {
$where_private = ' AND t.is_private = false';
}
if ($where_is_blocked) {
$where_is_blocked = 't.is_blocked = false ';
}
$where_on_top = " AND t.ontop = false ";
if (!$where_is_blocked && !$group && !$where_deleted && !$where_private) {
$where_on_top = " t.ontop = false ";
}
$where = 'WHERE';
if (!$where_is_blocked && !$group && !$where_deleted && !$where_private && !$where_on_top) {
$where = '';
}
$sql = "SELECT COUNT(1) FROM blogs_themes t \n {$where} {$where_is_blocked} {$_group} {$where_deleted} {$where_private} {$where_on_top}";
$num_treads = $DB->cache(1800)->val($sql);
}
}
if ($ids) {
$res = $DB->rows("SELECT * FROM blogs_poll_answers WHERE thread_id IN (" . implode(',', $ids) . ") ORDER BY id");
if ($res) {
foreach ($res as $row) {
$idx[$row['thread_id']]['poll'][] = $row;
}
}
}
if ($ontops) {
if ($page == 1) {
foreach ($ontops as $ot) {
array_unshift($ret, $ot);
}
}
$c = count($ret);
while ($c-- > $limit) {
array_pop($ret);
}
}
return $ret;
}
