also consider deleted folders and feeds in the feed update, #214

3 files changed, 43 insertions, 2 deletions

diff --git a/CHANGELOG b/CHANGELOG
index 2f115a2ac..0f458488a 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,3 +1,6 @@
+owncloud-news (1.001)
+* Also use monospace for pre tag
+
 ownCloud-news (0.104)
 * Also html decode the links to the page to not break on nyaa torrents
 
diff --git a/db/feedmapper.php b/db/feedmapper.php
index 99271f494..5a5aa299f 100644
--- a/db/feedmapper.php
+++ b/db/feedmapper.php
@@ -108,7 +108,26 @@ class FeedMapper extends Mapper implements IMapper {
 
 
 	public function findAll(){
-		$sql = 'SELECT * FROM `*PREFIX*news_feeds`';
+		$sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' .
+			'FROM `*PREFIX*news_feeds` `feeds` ' .
+			'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` '.
+				'ON `feeds`.`folder_id` = `folders`.`id` ' .
+			'LEFT JOIN `*PREFIX*news_items` `items` ' .
+				'ON `feeds`.`id` = `items`.`feed_id` ' .
+				// WARNING: this is a desperate attempt at making this query work
+				// because prepared statements dont work. This is a possible 
+				// SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT.
+				// think twice when changing this 
+				'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' .
+				StatusFlag::UNREAD . ' ' .
+			'WHERE (`feeds`.`folder_id` = 0 ' .
+				'OR `folders`.`deleted_at` = 0' .
+			')' .
+			'AND `feeds`.`deleted_at` = 0 ' .
+			'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'.
+				'`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'.
+				'`feeds`.`favicon_link`, `feeds`.`added`,'.
+				'`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`';
 
 		return $this->findAllRows($sql);
 	}
diff --git a/tests/unit/db/FeedMapperTest.php b/tests/unit/db/FeedMapperTest.php
index 3ebcb8ebe..ebe8961ab 100644
--- a/tests/unit/db/FeedMapperTest.php
+++ b/tests/unit/db/FeedMapperTest.php
@@ -133,7 +133,26 @@ class FeedMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 			array('id' => $this->feeds[0]->getId()),
 			array('id' => $this->feeds[1]->getId())
 		);
-		$sql = 'SELECT * FROM `*PREFIX*news_feeds`';
+		$sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' .
+			'FROM `*PREFIX*news_feeds` `feeds` ' .
+			'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` '.
+				'ON `feeds`.`folder_id` = `folders`.`id` ' .
+			'LEFT JOIN `*PREFIX*news_items` `items` ' .
+				'ON `feeds`.`id` = `items`.`feed_id` ' .
+				// WARNING: this is a desperate attempt at making this query work
+				// because prepared statements dont work. This is a possible 
+				// SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT.
+				// think twice when changing this 
+				'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' .
+				StatusFlag::UNREAD . ' ' .
+			'WHERE (`feeds`.`folder_id` = 0 ' .
+				'OR `folders`.`deleted_at` = 0' .
+			')' .
+			'AND `feeds`.`deleted_at` = 0 ' .
+			'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'.
+				'`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'.
+				'`feeds`.`favicon_link`, `feeds`.`added`,'.
+				'`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`';
 		
 		$this->setMapperResult($sql, array(), $rows);