From 4f111c5b305078a641d13ac41ce7d798e3cc19ce Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Thu, 18 Aug 2022 12:06:31 +0200
Subject: Easier full-text search possibility (#4505)

* Easier full-text search possibility
Contributes to https://github.com/FreshRSS/FreshRSS/issues/1331
Avoid concats in searches to make text indexes easier to build

* Fix tests

* Documentation
---
 app/Models/EntryDAO.php       | 24 +++++++++++-------------
 app/Models/EntryDAOSQLite.php |  4 ----
 2 files changed, 11 insertions(+), 17 deletions(-)

(limited to 'app/Models')

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 02552affe..7cefc8c0e 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -10,10 +10,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return true;
 	}
 
-	protected static function sqlConcat($s1, $s2) {
-		return 'CONCAT(' . $s1 . ',' . $s2 . ')';	//MySQL
-	}
-
 	public static function sqlHexDecode(string $x): string {
 		return 'unhex(' . $x . ')';
 	}
@@ -950,47 +946,49 @@ SQL;
 			}
 			if ($filter->getInurl()) {
 				foreach ($filter->getInurl() as $url) {
-					$sub_search .= 'AND ' . static::sqlConcat($alias . 'link', $alias . 'guid') . ' LIKE ? ';
+					$sub_search .= 'AND ' . $alias . 'link LIKE ? ';
 					$values[] = "%{$url}%";
 				}
 			}
 
 			if ($filter->getNotAuthor()) {
 				foreach ($filter->getNotAuthor() as $author) {
-					$sub_search .= 'AND (NOT ' . $alias . 'author LIKE ?) ';
+					$sub_search .= 'AND ' . $alias . 'author NOT LIKE ? ';
 					$values[] = "%{$author}%";
 				}
 			}
 			if ($filter->getNotIntitle()) {
 				foreach ($filter->getNotIntitle() as $title) {
-					$sub_search .= 'AND (NOT ' . $alias . 'title LIKE ?) ';
+					$sub_search .= 'AND ' . $alias . 'title NOT LIKE ? ';
 					$values[] = "%{$title}%";
 				}
 			}
 			if ($filter->getNotTags()) {
 				foreach ($filter->getNotTags() as $tag) {
-					$sub_search .= 'AND (NOT ' . $alias . 'tags LIKE ?) ';
+					$sub_search .= 'AND ' . $alias . 'tags NOT LIKE ? ';
 					$values[] = "%{$tag}%";
 				}
 			}
 			if ($filter->getNotInurl()) {
 				foreach ($filter->getNotInurl() as $url) {
-					$sub_search .= 'AND (NOT ' . static::sqlConcat($alias . 'link', $alias . 'guid') . ' LIKE ?) ';
+					$sub_search .= 'AND ' . $alias . 'link NOT LIKE ? ';
 					$values[] = "%{$url}%";
 				}
 			}
 
 			if ($filter->getSearch()) {
 				foreach ($filter->getSearch() as $search_value) {
-					$sub_search .= 'AND ' . static::sqlConcat($alias . 'title',
-						static::isCompressed() ? 'UNCOMPRESS(' . $alias . 'content_bin)' : '' . $alias . 'content') . ' LIKE ? ';
+					$sub_search .= 'AND (' . $alias . 'title LIKE ? OR ' .
+						(static::isCompressed() ? 'UNCOMPRESS(' . $alias . 'content_bin)' : '' . $alias . 'content') . ' LIKE ?) ';
+					$values[] = "%{$search_value}%";
 					$values[] = "%{$search_value}%";
 				}
 			}
 			if ($filter->getNotSearch()) {
 				foreach ($filter->getNotSearch() as $search_value) {
-					$sub_search .= 'AND (NOT ' . static::sqlConcat($alias . 'title',
-						static::isCompressed() ? 'UNCOMPRESS(' . $alias . 'content_bin)' : '' . $alias . 'content') . ' LIKE ?) ';
+					$sub_search .= 'AND ' . $alias . 'title NOT LIKE ? AND ' .
+						(static::isCompressed() ? 'UNCOMPRESS(' . $alias . 'content_bin)' : '' . $alias . 'content') . ' NOT LIKE ? ';
+					$values[] = "%{$search_value}%";
 					$values[] = "%{$search_value}%";
 				}
 			}
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index 27a7c3560..b1ab6601e 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -10,10 +10,6 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 		return false;
 	}
 
-	protected static function sqlConcat($s1, $s2) {
-		return $s1 . '||' . $s2;
-	}
-
 	public static function sqlHexDecode(string $x): string {
 		return $x;
 	}
-- 
cgit v1.2.3