/** * Возвращает список тем в разделе * * ------------------------------------------------------------------------------------- * Внимание! Замечание к подзапросу на основные вкладки (новые, популярные, актуальные). * * Подзапрос настроен на работу индексов типа "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; }