From 87b0a23ca88f4ed43edb8339bb2132dfd549c9ca Mon Sep 17 00:00:00 2001
From: Michael <heluecht@pirati.ca>
Date: Wed, 10 Jan 2024 21:17:21 +0000
Subject: [PATCH] Improved performance with full text search

---
 .../Repository/UserDefinedChannel.php         | 24 +++++++++++--------
 src/Model/Post/Engagement.php                 |  3 +--
 src/Protocol/ActivityPub/Processor.php        |  2 +-
 3 files changed, 16 insertions(+), 13 deletions(-)

diff --git a/src/Content/Conversation/Repository/UserDefinedChannel.php b/src/Content/Conversation/Repository/UserDefinedChannel.php
index bb8ad02f6b..37e3ff06ff 100644
--- a/src/Content/Conversation/Repository/UserDefinedChannel.php
+++ b/src/Content/Conversation/Repository/UserDefinedChannel.php
@@ -163,17 +163,13 @@ class UserDefinedChannel extends \Friendica\BaseRepository
 	}
 
 	/**
-	 * Checks, if one of the user defined channels matches with the given search text
-	 * @todo Combine all the full text statements in a single search text to improve the performance.
-	 * Add a "valid" field for the channel that is set when the full text statement doesn't contain errors.
+	 * Checks, if one of the user defined channels matches with the given search text or languages
 	 *
 	 * @param string $searchtext
 	 * @param string $language
-	 * @param array  $tags
-	 * @param int    $media_type
 	 * @return boolean
 	 */
-	public function match(string $searchtext, string $language, array $tags, int $media_type): bool
+	public function match(string $searchtext, string $language): bool
 	{
 		$users = $this->db->selectToArray('user', ['uid'], $this->getUserCondition());
 		if (empty($users)) {
@@ -182,16 +178,24 @@ class UserDefinedChannel extends \Friendica\BaseRepository
 
 		$uids = array_column($users, 'uid');
 
-		$condition = ['uid' => $uids];
-		$condition = DBA::mergeConditions($condition, ["`languages` != ? AND `include-tags` = ? AND `full-text-search` = ? AND circle = ?", '', '', '', 0]);
-
+		$usercondition = ['uid' => $uids];
+		$condition = DBA::mergeConditions($usercondition, ["`languages` != ? AND `include-tags` = ? AND `full-text-search` = ? AND `circle` = ?", '', '', '', 0]);
 		foreach ($this->select($condition) as $channel) {
 			if (!empty($channel->languages) && in_array($language, $channel->languages)) {
 				return true;
 			}
 		}
 
-		return !empty($this->getMatches($searchtext, $language, $tags, $media_type, 0, 0, $uids, false));
+		$search = '';
+		$condition = DBA::mergeConditions($usercondition, ["`full-text-search` != ? AND `circle` = ? AND `valid`", '', 0]);
+		foreach ($this->select($condition) as $channel) {
+			$search .= '(' . $channel->fullTextSearch . ') ';
+		}
+
+		$this->db->insert('check-full-text-search', ['pid' => getmypid(), 'searchtext' => $searchtext], Database::INSERT_UPDATE);
+		$result = $this->inFulltext($search);
+		$this->db->delete('check-full-text-search', ['pid' => getmypid()]);
+		return $result;
 	}
 
 	/**
diff --git a/src/Model/Post/Engagement.php b/src/Model/Post/Engagement.php
index 0476e08ca2..61f73948de 100644
--- a/src/Model/Post/Engagement.php
+++ b/src/Model/Post/Engagement.php
@@ -91,9 +91,8 @@ class Engagement
 
 		$searchtext = self::getSearchTextForItem($parent);
 		if (!$store) {
-			$tags     = array_column(Tag::getByURIId($item['parent-uri-id'], [Tag::HASHTAG]), 'name');
 			$language = !empty($parent['language']) ? (array_key_first(json_decode($parent['language'], true)) ?? '') : '';
-			$store    = DI::userDefinedChannel()->match($searchtext, $language, $tags, $mediatype);
+			$store    = DI::userDefinedChannel()->match($searchtext, $language);
 		}
 
 		$engagement = [
diff --git a/src/Protocol/ActivityPub/Processor.php b/src/Protocol/ActivityPub/Processor.php
index f0fbf1185a..593c1e7836 100644
--- a/src/Protocol/ActivityPub/Processor.php
+++ b/src/Protocol/ActivityPub/Processor.php
@@ -1787,7 +1787,7 @@ class Processor
 		$searchtext = Engagement::getSearchTextForActivity($content, $authorid, $messageTags, $receivers);
 		$languages  = Item::getLanguageArray($content, 1, 0, $authorid);
 		$language   = !empty($languages) ? array_key_first($languages) : '';
-		return DI::userDefinedChannel()->match($searchtext, $language, $messageTags, 0);
+		return DI::userDefinedChannel()->match($searchtext, $language);
 	}
 
 	/**
-- 
2.39.5