From 37b48d2aef2b82db461c8227b78e01c5cdd8e9c1 Mon Sep 17 00:00:00 2001
From: Bernhard Posselt <nukeawhale@gmail.com>
Date: Fri, 24 May 2013 15:28:42 +0200
Subject: fix a bug that would not display items of feeds without folders

---
 CHANGELOG                        |  1 +
 db/feedmapper.php                |  5 +++++
 db/itemmapper.php                |  5 +++--
 tests/unit/db/FeedMapperTest.php | 21 +++++++++++++++------
 tests/unit/db/ItemMapperTest.php |  5 +++--
 5 files changed, 27 insertions(+), 10 deletions(-)

diff --git a/CHANGELOG b/CHANGELOG
index 5a2f2debf..f7ae91716 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,6 +1,7 @@
 ownCloud-news (0.102)
 * Fix marking read of all articles and folders on mysql and postgres
 * Fix bug that would still show items after its feed or folder has been marked as deleted
+* Fix bug that would show invalid unread count for feeds whose folders were deleted
 
 ownCloud-news (0.101)
 
diff --git a/db/feedmapper.php b/db/feedmapper.php
index f8c222298..99271f494 100644
--- a/db/feedmapper.php
+++ b/db/feedmapper.php
@@ -82,6 +82,8 @@ class FeedMapper extends Mapper implements IMapper {
 	public function findAllFromUser($userId){
 		$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
@@ -91,6 +93,9 @@ class FeedMapper extends Mapper implements IMapper {
 				'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' .
 				StatusFlag::UNREAD . ' ' .
 			'WHERE `feeds`.`user_id` = ? ' .
+			'AND (`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`,'.
diff --git a/db/itemmapper.php b/db/itemmapper.php
index cd46678b7..0f94b4301 100644
--- a/db/itemmapper.php
+++ b/db/itemmapper.php
@@ -59,9 +59,10 @@ class ItemMapper extends Mapper implements IMapper {
 				'AND `feeds`.`deleted_at` = 0 ' .
 				'AND `feeds`.`user_id` = ? ' . 
 				$prependTo .
-			'JOIN `*PREFIX*news_folders` `folders` ' .
+			'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' .
 				'ON `folders`.`id` = `feeds`.`folder_id` ' .
-				'AND `folders`.`deleted_at` = 0 ' . 
+			'WHERE `feeds`.`folder_id` = 0 ' .
+				'OR `folders`.`deleted_at` = 0 ' . 
 			'ORDER BY `items`.`id` DESC';
 	}
 
diff --git a/tests/unit/db/FeedMapperTest.php b/tests/unit/db/FeedMapperTest.php
index 460156e5c..3ebcb8ebe 100644
--- a/tests/unit/db/FeedMapperTest.php
+++ b/tests/unit/db/FeedMapperTest.php
@@ -150,16 +150,25 @@ class FeedMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 		);
 		$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` ' . 
-				'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . 
+				'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`.`user_id` = ? ' .
+			'AND (`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`';
+			'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($userId), $rows);
 		
diff --git a/tests/unit/db/ItemMapperTest.php b/tests/unit/db/ItemMapperTest.php
index 06565fc1f..8b427923c 100644
--- a/tests/unit/db/ItemMapperTest.php
+++ b/tests/unit/db/ItemMapperTest.php
@@ -86,9 +86,10 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 				'AND `feeds`.`deleted_at` = 0 ' .
 				'AND `feeds`.`user_id` = ? ' . 
 				$prependTo .
-			'JOIN `*PREFIX*news_folders` `folders` ' .
+			'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' .
 				'ON `folders`.`id` = `feeds`.`folder_id` ' .
-				'AND `folders`.`deleted_at` = 0 ' . 
+			'WHERE `feeds`.`folder_id` = 0 ' .
+				'OR `folders`.`deleted_at` = 0 ' . 
 			'ORDER BY `items`.`id` DESC';
 	}
 
-- 
cgit v1.2.3