From 45471871ddc4bae6c1c36f2daa9091b85e458e8c Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 15 Nov 2025 20:11:25 +0100
Subject: SQL: Optimise speed of updateCachedValues() (#8207)

For PostgreSQL and SQLite
fix https://github.com/FreshRSS/FreshRSS/issues/8206
---
 app/Models/FeedDAO.php       |  8 ++++----
 app/Models/FeedDAOPGSQL.php  | 36 ++++++++++++++++++++++++++++++++++++
 app/Models/FeedDAOSQLite.php |  2 +-
 3 files changed, 41 insertions(+), 5 deletions(-)

diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 2e0d5781a..0353f9a05 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -481,10 +481,10 @@ SQL;
 	public function updateCachedValues(int ...$feedIds): int|false {
 		//2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE
 		$sql = <<<SQL
-UPDATE `_feed`
-SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `_entry` e1 WHERE e1.id_feed=`_feed`.id),
-	`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `_entry` e2 WHERE e2.id_feed=`_feed`.id AND e2.is_read=0)
-SQL;
+			UPDATE `_feed`
+			SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `_entry` e1 WHERE e1.id_feed=`_feed`.id),
+				`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `_entry` e2 WHERE e2.id_feed=`_feed`.id AND e2.is_read=0)
+			SQL;
 		if (count($feedIds) > 0) {
 			$sql .= ' WHERE id IN (' . str_repeat('?,', count($feedIds) - 1) . '?)';
 		}
diff --git a/app/Models/FeedDAOPGSQL.php b/app/Models/FeedDAOPGSQL.php
index f436a2ec4..954a6e0ff 100644
--- a/app/Models/FeedDAOPGSQL.php
+++ b/app/Models/FeedDAOPGSQL.php
@@ -10,4 +10,40 @@ SELECT setval('`_feed_id_seq`', COALESCE(MAX(id), 0) + 1, false) FROM `_feed`
 SQL;
 		return $this->pdo->exec($sql) !== false;
 	}
+
+	#[\Override]
+	public function updateCachedValues(int ...$feedIds): int|false {
+		// Faster than the MySQL version
+		if (empty($feedIds)) {
+			$whereFeedIds = 'true';
+			$whereEntryIdFeeds = 'true';
+		} else {
+			$whereFeedIds = 'id IN (' . str_repeat('?,', count($feedIds) - 1) . '?)';
+			$whereEntryIdFeeds = 'id_feed IN (' . str_repeat('?,', count($feedIds) - 1) . '?)';
+		}
+		$sql = <<<SQL
+			WITH entry_counts AS (
+				SELECT
+					id_feed,
+					COUNT(*) AS total_entries,
+					SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) AS unread_entries
+				FROM `_entry`
+				WHERE $whereEntryIdFeeds
+				GROUP BY id_feed
+			)
+			UPDATE `_feed`
+			SET `cache_nbEntries` = COALESCE(c.total_entries, 0),
+				`cache_nbUnreads` = COALESCE(c.unread_entries, 0)
+			FROM entry_counts c
+			WHERE id = c.id_feed AND $whereFeedIds
+			SQL;
+		$stm = $this->pdo->prepare($sql);
+		if ($stm !== false && $stm->execute(array_merge($feedIds, $feedIds))) {
+			return $stm->rowCount();
+		} else {
+			$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
+			Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
+			return false;
+		}
+	}
 }
diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php
index 18befed54..ed89e9552 100644
--- a/app/Models/FeedDAOSQLite.php
+++ b/app/Models/FeedDAOSQLite.php
@@ -1,7 +1,7 @@
 <?php
 declare(strict_types=1);
 
-class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO {
+class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAOPGSQL {
 
 	#[\Override]
 	public function sqlResetSequence(): bool {
-- 
cgit v1.2.3