/** * Deconstruct a search string and return a list of conversation IDs that fulfill it. * * @param array $channelIDs A list of channel IDs to include results from. * @param string $searchString The search string to deconstruct and find matching conversations. * @param bool $orderBySticky Whether or not to put stickied conversations at the top. * @return array|bool An array of matching conversation IDs, or false if there are none. */ public function getConversationIDs($channelIDs = array(), $searchString = "", $orderBySticky = false) { $this->reset(); $this->trigger("getConversationIDsBefore", array(&$channelIDs, &$searchString, &$orderBySticky)); if ($searchString and $seconds = $this->isFlooding()) { $this->error("search", sprintf(T("message.waitToSearch"), $seconds)); return false; } // Initialize the SQL query that will return the resulting conversation IDs. $this->sql = ET::SQL()->select("c.conversationId")->from("conversation c"); // Only get conversations in the specified channels. if ($channelIDs) { $this->sql->where("c.channelId IN (:channelIds)")->bind(":channelIds", $channelIDs); } // Process the search string into individial terms. Replace all "-" signs with "+!", and then // split the string by "+". Negated terms will then be prefixed with "!". Only keep the first // 5 terms, just to keep the load on the database down! $terms = !empty($searchString) ? explode("+", strtolower(str_replace("-", "+!", trim($searchString, " +-")))) : array(); $terms = array_slice(array_filter($terms), 0, 5); // Take each term, match it with a gambit, and execute the gambit's function. foreach ($terms as $term) { // Are we dealing with a negated search term, ie. prefixed with a "!"? $term = trim($term); if ($negate = $term[0] == "!") { $term = trim($term, "! "); } if ($term[0] == "#") { $term = ltrim($term, "#"); // If the term is an alias, translate it into the appropriate gambit. if (array_key_exists($term, self::$aliases)) { $term = self::$aliases[$term]; } // Find a matching gambit by evaluating each gambit's condition, and run its callback function. foreach (self::$gambits as $gambit) { list($condition, $function) = $gambit; if (eval($condition)) { call_user_func_array($function, array(&$this, $term, $negate)); continue 2; } } } // If we didn't find a gambit, use this term as a fulltext term. if ($negate) { $term = "-" . str_replace(" ", " -", $term); } $this->fulltext($term); } // If an order for the search results has not been specified, apply a default. // Order by sticky and then last post time. if (!count($this->orderBy)) { if ($orderBySticky) { $this->orderBy("c.sticky DESC"); } $this->orderBy("c.lastPostTime DESC"); } // If we're not including ignored conversations, add a where predicate to the query to exclude them. if (!$this->includeIgnored and ET::$session->user) { $q = ET::SQL()->select("conversationId")->from("member_conversation")->where("type='member'")->where("id=:memberIdIgnored")->where("ignored=1")->get(); $this->sql->where("conversationId NOT IN ({$q})")->bind(":memberIdIgnored", ET::$session->userId); } // Now we need to loop through the ID filters and run them one-by-one. When a query returns a selection // of conversation IDs, subsequent queries are restricted to filtering those conversation IDs, // and so on, until we have a list of IDs to pass to the final query. $goodConversationIDs = array(); $badConversationIDs = array(); $idCondition = ""; foreach ($this->idFilters as $v) { list($sql, $negate) = $v; // Apply the list of good IDs to the query. $sql->where($idCondition); // Get the list of conversation IDs so that the next condition can use it in its query. $result = $sql->exec(); $ids = array(); while ($row = $result->nextRow()) { $ids[] = (int) reset($row); } // If this condition is negated, then add the IDs to the list of bad conversations. // If the condition is not negated, set the list of good conversations to the IDs, provided there are some. if ($negate) { $badConversationIDs = array_merge($badConversationIDs, $ids); } elseif (count($ids)) { $goodConversationIDs = $ids; } else { return false; } // Strip bad conversation IDs from the list of good conversation IDs. if (count($goodConversationIDs)) { $goodConversationIds = array_diff($goodConversationIDs, $badConversationIDs); if (!count($goodConversationIDs)) { return false; } } // This will be the condition for the next query that restricts or eliminates conversation IDs. if (count($goodConversationIDs)) { $idCondition = "conversationId IN (" . implode(",", $goodConversationIDs) . ")"; } elseif (count($badConversationIDs)) { $idCondition = "conversationId NOT IN (" . implode(",", $badConversationIDs) . ")"; } } // Reverse the order if necessary - swap DESC and ASC. if ($this->orderReverse) { foreach ($this->orderBy as $k => $v) { $this->orderBy[$k] = strtr($this->orderBy[$k], array("DESC" => "ASC", "ASC" => "DESC")); } } // Now check if there are any fulltext keywords to filter by. if (count($this->fulltext)) { // Run a query against the posts table to get matching conversation IDs. $fulltextString = implode(" ", $this->fulltext); $fulltextQuery = ET::SQL()->select("DISTINCT conversationId")->from("post")->where("MATCH (title, content) AGAINST (:fulltext IN BOOLEAN MODE)")->where($idCondition)->orderBy("MATCH (title, content) AGAINST (:fulltextOrder) DESC")->bind(":fulltext", $fulltextString)->bind(":fulltextOrder", $fulltextString); $this->trigger("fulltext", array($fulltextQuery, $this->fulltext)); $result = $fulltextQuery->exec(); $ids = array(); while ($row = $result->nextRow()) { $ids[] = reset($row); } // Change the ID condition to this list of matching IDs, and order by relevance. if (count($ids)) { $idCondition = "conversationId IN (" . implode(",", $ids) . ")"; } else { return false; } $this->orderBy = array("FIELD(c.conversationId," . implode(",", $ids) . ")"); } // Set a default limit if none has previously been set. if (!$this->limit) { $this->limit = C("esoTalk.search.limit"); } // Finish constructing the final query using the ID whitelist/blacklist we've come up with. // Get one more result than we'll actually need so we can see if there are "more results." if ($idCondition) { $this->sql->where($idCondition); } $this->sql->orderBy($this->orderBy)->limit($this->limit + 1); // Make sure conversations that the user isn't allowed to see are filtered out. ET::conversationModel()->addAllowedPredicate($this->sql); // Execute the query, and collect the final set of conversation IDs. $result = $this->sql->exec(); $conversationIDs = array(); while ($row = $result->nextRow()) { $conversationIDs[] = reset($row); } // If there's one more result than we actually need, indicate that there are "more results." if (count($conversationIDs) == $this->limit + 1) { array_pop($conversationIDs); if ($this->limit < C("esoTalk.search.limitMax")) { $this->areMoreResults = true; } } return count($conversationIDs) ? $conversationIDs : false; }
/** * 通常の一覧テーマ検索取得処理 * * キーワード指定がある場合、投稿のタイトル・本文・コメント・タグに対して検索処理実施する * 下書きは検索対象外 * * Deconstruct a search string and return a list of conversation IDs that fulfill it. * * @param array $channelIDs A list of channel IDs to include results from. * @param string $searchString The search string to deconstruct and find matching conversations. * @param bool $orderBySticky Whether or not to put stickied conversations at the top. * @return array|bool An array of matching conversation IDs, or false if there are none. */ public function getConversationIDs($channelIDs = array(), $searchString = "", $orderBySticky = false, $searchLimit = false) { $this->reset(); $this->trigger("getConversationIDsBefore", array(&$channelIDs, &$searchString, &$orderBySticky)); if ($searchString and $seconds = $this->isFlooding()) { $this->error("search", sprintf(T("message.waitToSearch"), $seconds)); return false; } // Initialize the SQL query that will return the resulting conversation IDs. $this->sql = ET::SQL()->select("c.conversationId")->from("conversation c"); // Only get conversations in the specified channels. if ($channelIDs) { $this->sql->where("c.channelId IN (:channelIds)")->bind(":channelIds", $channelIDs); } // Process the search string into individial terms. Replace all "-" signs with "+!", and then // split the string by "+". Negated terms will then be prefixed with "!". Only keep the first // 5 terms, just to keep the load on the database down! // $terms = !empty($searchString) ? explode("+", strtolower(str_replace("-", "+!", trim($searchString, " +-")))) : array(); $tearms = array(); if (!empty($searchString)) { // キーワードがある場合 // 全角スペースを半角スペースへ置換 $searchString = mb_convert_kana($searchString, 's', 'UTF-8'); $terms = explode(" ", strtolower(str_replace("-", " !", trim($searchString, " +-")))); // 最初の5個までを対象とする $terms = array_slice(array_filter($terms), 0, 5); // Take each term, match it with a gambit, and execute the gambit's function. foreach ($terms as $term) { // Are we dealing with a negated search term, ie. prefixed with a "!"? $term = trim($term); if ($negate = $term[0] == "!") { $term = trim($term, "! "); } if ($term[0] == "#") { $term = ltrim($term, "#"); // If the term is an alias, translate it into the appropriate gambit. if (array_key_exists($term, self::$aliases)) { $term = self::$aliases[$term]; } // Find a matching gambit by evaluating each gambit's condition, and run its callback function. foreach (self::$gambits as $gambit) { list($condition, $function) = $gambit; if (eval($condition)) { call_user_func_array($function, array(&$this, $term, $negate)); continue 2; } } } // If we didn't find a gambit, use this term as a fulltext term. if ($negate) { $term = "-" . str_replace(" ", " -", $term); } $this->fulltext($term); } } // If an order for the search results has not been specified, apply a default. // Order by sticky and then last post time. if (!count($this->orderBy)) { if ($orderBySticky) { $this->orderBy("c.sticky DESC"); } $this->orderBy("c.lastPostTime DESC"); } // If we're not including ignored conversations, add a where predicate to the query to exclude them. if (!$this->includeIgnored and ET::$session->user) { $q = ET::SQL()->select("conversationId")->from("member_conversation")->where("type='member'")->where("id=:memberIdIgnored")->where("ignored=1")->get(); $this->sql->where("c.conversationId NOT IN ({$q})")->bind(":memberIdIgnored", ET::$session->userId); } // Now we need to loop through the ID filters and run them one-by-one. When a query returns a selection // of conversation IDs, subsequent queries are restricted to filtering those conversation IDs, // and so on, until we have a list of IDs to pass to the final query. $goodConversationIDs = array(); $badConversationIDs = array(); $idCondition = ""; foreach ($this->idFilters as $v) { list($sql, $negate) = $v; // Apply the list of good IDs to the query. $sql->where($idCondition); // Get the list of conversation IDs so that the next condition can use it in its query. $result = $sql->exec(); $ids = array(); while ($row = $result->nextRow()) { $ids[] = (int) reset($row); } // If this condition is negated, then add the IDs to the list of bad conversations. // If the condition is not negated, set the list of good conversations to the IDs, provided there are some. if ($negate) { $badConversationIDs = array_merge($badConversationIDs, $ids); } elseif (count($ids)) { $goodConversationIDs = $ids; } else { return false; } // Strip bad conversation IDs from the list of good conversation IDs. if (count($goodConversationIDs)) { $goodConversationIds = array_diff($goodConversationIDs, $badConversationIDs); if (!count($goodConversationIDs)) { return false; } } // This will be the condition for the next query that restricts or eliminates conversation IDs. if (count($goodConversationIDs)) { $idCondition = "conversationId IN (" . implode(",", $goodConversationIDs) . ")"; } elseif (count($badConversationIDs)) { $idCondition = "conversationId NOT IN (" . implode(",", $badConversationIDs) . ")"; } } // Reverse the order if necessary - swap DESC and ASC. if ($this->orderReverse) { foreach ($this->orderBy as $k => $v) { $this->orderBy[$k] = strtr($this->orderBy[$k], array("DESC" => "ASC", "ASC" => "DESC")); } } // Now check if there are any fulltext keywords to filter by. $cnt = count($this->fulltext); if ($cnt > 0) { // タグID取得 $tagsIds = ET::tagsModel()->getTagsIds($this->fulltext); // 投稿のタイトル・本文・コメントをLIKE(部分一致)検索する // タグを完全一致検索する // 下書きは検索しない $this->sql->from("post p", "p.conversationId=c.conversationId", "left"); $this->sql->where("c.countPosts > 0"); $strWhere = ""; for ($i = 0; $i < $cnt; $i++) { $val = $this->fulltext[$i]; $param = ":text" . $i; if ($i != 0) { $strWhere .= " OR "; } $strWhere .= "c.title LIKE " . $param . " OR p.content LIKE " . $param; $this->sql->bind($param, '%' . $val . '%'); } if (is_array($tagsIds) && count($tagsIds)) { // タグIDがある場合 $this->sql->from("conversation_tags t", "t.conversationId=c.conversationId", "left"); $strWhere .= " OR (t.tag0 IN (:tagsIds) OR t.tag1 IN (:tagsIds) OR t.tag2 IN (:tagsIds) OR t.tag3 IN (:tagsIds) OR t.tag4 IN (:tagsIds) OR t.tag5 IN (:tagsIds) OR t.tag6 IN (:tagsIds) OR t.tag7 IN (:tagsIds) OR t.tag8 IN (:tagsIds) OR t.tag9 IN (:tagsIds))"; $this->sql->bind(":tagsIds", $tagsIds); } $this->sql->where($strWhere); } // 取得件数設定 $this->limit = $searchLimit; // Set a default limit if none has previously been set. if (!$this->limit) { $this->limit = C("esoTalk.search.limit"); } // Finish constructing the final query using the ID whitelist/blacklist we've come up with. // Get one more result than we'll actually need so we can see if there are "more results." if ($idCondition) { $this->sql->where($idCondition); } $this->sql->orderBy($this->orderBy)->limit($this->limit + 1); // Make sure conversations that the user isn't allowed to see are filtered out. ET::conversationModel()->addAllowedPredicate($this->sql); // Execute the query, and collect the final set of conversation IDs. $result = $this->sql->exec(); $conversationIDs = array(); while ($row = $result->nextRow()) { $conversationIDs[] = reset($row); } // If there's one more result than we actually need, indicate that there are "more results." if (count($conversationIDs) == $this->limit + 1) { array_pop($conversationIDs); if ($this->limit < C("esoTalk.search.limitMax")) { $this->areMoreResults = true; } } return count($conversationIDs) ? $conversationIDs : false; }