API return proper newestItemTimestampUsec (#2854)

* API return proper newestItemTimestampUsec

#fix https://github.com/FreshRSS/FreshRSS/issues/2853

* Minor naming

* Add newestItemTimestampUsec for labels

* Do not use t.name after all

3 files changed, 45 insertions, 4 deletions

diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 01f0b10f6..11db5e3d4 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -302,6 +302,22 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC));
 	}
 
+	public function listFeedsNewestItemUsec($id_feed = null) {
+		$sql = 'SELECT id_feed, MAX(id) as newest_item_us FROM `_entry` ';
+		if ($id_feed === null) {
+			$sql .= 'GROUP BY id_feed';
+		} else {
+			$sql .= 'WHERE id_feed=' . intval($id_feed);
+		}
+		$stm = $this->pdo->query($sql);
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		$newestItemUsec = [];
+		foreach ($res as $line) {
+			$newestItemUsec['f_' . $line['id_feed']] = $line['newest_item_us'];
+		}
+		return $newestItemUsec;
+	}
+
 	public function arrayFeedCategoryNames() {	//For API
 		$sql = 'SELECT f.id, f.name, c.name as c_name FROM `_feed` f '
 		     . 'INNER JOIN `_category` c ON c.id = f.category';
diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php
index 09397c6a1..05a88b182 100644
--- a/app/Models/TagDAO.php
+++ b/app/Models/TagDAO.php
@@ -193,6 +193,25 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		}
 	}
 
+	public function listTagsNewestItemUsec($id_tag = null) {
+		$sql = 'SELECT t.id AS id_tag, MAX(e.id) AS newest_item_us '
+			 . 'FROM `_tag` t '
+			 . 'LEFT OUTER JOIN `_entrytag` et ON et.id_tag = t.id '
+			 . 'LEFT OUTER JOIN `_entry` e ON et.id_entry = e.id ';
+		if ($id_tag === null) {
+			$sql .= 'GROUP BY t.id';
+		} else {
+			$sql .= 'WHERE t.id=' . intval($id_tag);
+		}
+		$stm = $this->pdo->query($sql);
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		$newestItemUsec = [];
+		foreach ($res as $line) {
+			$newestItemUsec['t_' . $line['id_tag']] = $line['newest_item_us'];
+		}
+		return $newestItemUsec;
+	}
+
 	public function count() {
 		$sql = 'SELECT COUNT(*) AS count FROM `_tag`';
 		$stm = $this->pdo->query($sql);
diff --git a/p/api/greader.php b/p/api/greader.php
index 2d1acca16..69962e9d5 100644
--- a/p/api/greader.php
+++ b/p/api/greader.php
@@ -411,14 +411,17 @@ function unreadCount() {	//http://blog.martindoms.com/2009/10/16/using-the-googl
 	$totalLastUpdate = 0;
 
 	$categoryDAO = FreshRSS_Factory::createCategoryDao();
+	$feedDAO = FreshRSS_Factory::createFeedDao();
+	$feedsNewestItemUsec = $feedDAO->listFeedsNewestItemUsec();
+
 	foreach ($categoryDAO->listCategories(true, true) as $cat) {
 		$catLastUpdate = 0;
 		foreach ($cat->feeds() as $feed) {
-			$lastUpdate = $feed->lastUpdate();
+			$lastUpdate = isset($feedsNewestItemUsec['f_' . $feed->id()]) ? $feedsNewestItemUsec['f_' . $feed->id()] : 0;
 			$unreadcounts[] = array(
 				'id' => 'feed/' . $feed->id(),
 				'count' => $feed->nbNotRead(),
-				'newestItemTimestampUsec' => $lastUpdate . '000000',
+				'newestItemTimestampUsec' => $lastUpdate,
 			);
 			if ($catLastUpdate < $lastUpdate) {
 				$catLastUpdate = $lastUpdate;
@@ -427,7 +430,7 @@ function unreadCount() {	//http://blog.martindoms.com/2009/10/16/using-the-googl
 		$unreadcounts[] = array(
 			'id' => 'user/-/label/' . htmlspecialchars_decode($cat->name(), ENT_QUOTES),
 			'count' => $cat->nbNotRead(),
-			'newestItemTimestampUsec' => $catLastUpdate . '000000',
+			'newestItemTimestampUsec' => $catLastUpdate,
 		);
 		$totalUnreads += $cat->nbNotRead();
 		if ($totalLastUpdate < $catLastUpdate) {
@@ -436,17 +439,20 @@ function unreadCount() {	//http://blog.martindoms.com/2009/10/16/using-the-googl
 	}
 
 	$tagDAO = FreshRSS_Factory::createTagDao();
+	$tagsNewestItemUsec = $tagDAO->listTagsNewestItemUsec();
 	foreach ($tagDAO->listTags(true) as $label) {
+		$lastUpdate = isset($tagsNewestItemUsec['t_' . $label->id()]) ? $tagsNewestItemUsec['t_' . $label->id()] : 0;
 		$unreadcounts[] = array(
 			'id' => 'user/-/label/' . htmlspecialchars_decode($label->name(), ENT_QUOTES),
 			'count' => $label->nbUnread(),
+			'newestItemTimestampUsec' => $lastUpdate,
 		);
 	}
 
 	$unreadcounts[] = array(
 		'id' => 'user/-/state/com.google/reading-list',
 		'count' => $totalUnreads,
-		'newestItemTimestampUsec' => $totalLastUpdate . '000000',
+		'newestItemTimestampUsec' => $totalLastUpdate,
 	);
 
 	echo json_encode(array(