/**
* 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;
}
/**
* Add a WHERE predicate to an SQL query which makes sure only rows for which the user has the specified
* permission are returned.
*
* @param ETSQLQuery $sql The SQL query to add the predicate to.
* @param string $field The name of the permission to check for.
* @param array $member The member to filter out channels for. If not specified, the currently
* logged-in user will be used.
* @param string $table The channel table alias used in the SQL query.
* @return void
*/
public function addPermissionPredicate(&$sql, $field = "view", $member = false, $table = "c")
{
// If no member was specified, use the current user.
if (!$member) {
$member = ET::$session->user;
}
// Get an array of group IDs for this member.
$groups = ET::groupModel()->getGroupIds($member["account"], array_keys((array) $member["groups"]));
// If the user is an administrator, don't add any SQL, as admins can do anything!
if (in_array(GROUP_ID_ADMINISTRATOR, $groups)) {
return;
}
// Construct a query that will fetch all channelIds for which this member has the specified permission.
$query = ET::SQL()->select("channelId")->from("channel_group")->where("groupId IN (:groups)")->where("{$field}=1")->get();
// Add this as a where clause to the SQL query.
$sql->where("{$table}.channelId IN ({$query})")->bind(":groups", $groups, PDO::PARAM_INT);
}
/**
* Get standardized member data given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of members and their details.
*/
public function getWithSQL($sql)
{
$sql->select("m.*")->select("GROUP_CONCAT(g.groupId) AS groups")->select("GROUP_CONCAT(g.name) AS groupNames")->select("BIT_OR(g.canSuspend) AS canSuspend")->from("member m")->from("member_group mg", "mg.memberId=m.memberId", "left")->from("group g", "g.groupId=mg.groupId", "left")->groupBy("m.memberId");
$members = $sql->exec()->allRows();
// Expand the member data.
foreach ($members as &$member) {
$this->expand($member);
}
return $members;
}
/**
* Add a fulltext search WHERE predicate to an SQL query.
*
* @param ETSQLQuery $sql The SQL query to add the predicate to.
* @param string $search The search string.
* @return void
*/
private function whereSearch(&$sql, $search)
{
$sql->where("MATCH (content) AGAINST (:search IN BOOLEAN MODE)")->where("deleteMemberId IS NULL")->bind(":search", $search);
}
/**
* Add a WHERE predicate to an SQL query that gets conversations which should appear as notifications.
* The conversations must be private and the user must be allowed, or the user must have starred the
* conversation. The user must also have permission to view the channel that the conversation is in.
*
* @param ETSQLQuery $sql The SQL query to add the predicate to.
* @return void
*/
private function addNotificationConversationPredicate(&$sql)
{
$sql->where("((s.allowed=1 AND c.private=1) OR s.starred=1) AND s.muted!=1 AND ((s.type='member' AND s.id=:userId) OR (s.type='group' AND s.id IN (:groupIds)))")->bind(":userId", ET::$session->userId)->bind(":groupIds", ET::$session->getGroupIds());
ET::channelModel()->addPermissionPredicate($sql);
}
/**
* Get records given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of records.
*/
public function getWithSQL($sql)
{
return $sql->select("*")->from($this->table)->exec()->allRows();
}
/**
* 通常の一覧テーマ検索取得処理
*
* キーワード指定がある場合、投稿のタイトル・本文・コメント・タグに対して検索処理実施する
* 下書きは検索対象外
*
* 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;
}
/**
* Add a fulltext search WHERE predicate to an SQL query.
*
* @param ETSQLQuery $sql The SQL query to add the predicate to.
* @param string $search The search string.
* @return void
*/
private function whereSearch(&$sql, $search)
{
//中文分词，搜索更加准确
$fulltextString = ($s = spiltWords($search)) ? $s : $search;
$KeywordArray = explode(" ", $fulltextString);
$like = '';
$count = count($KeywordArray);
foreach ($KeywordArray as $key => $value) {
$like .= "(content LIKE '%{$value}%')";
if (ET::$session->user) {
$like .= " OR (content LIKE '%{$value}%')";
}
if ($key + 1 != $count) {
$like .= " OR ";
}
}
if (preg_match('/[\\x80-\\xff]/i', $search)) {
$sql->where($like);
} else {
$sql->where("MATCH (content) AGAINST (:search IN BOOLEAN MODE)")->where("MATCH (title) AGAINST (:search IN BOOLEAN MODE)");
}
$sql->where("deleteMemberId IS NULL")->bind(":search", "%" . $search . "%");
$this->trigger("whereSearch", array($sql, $search));
}
