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