Rewrite getNumberOfUnreadCommentsForFolder query

Before the joining and filtering removed unkown files. Resulting in
manual queries for all the files with no (unread) comments (the 99%).

Long story short. This will return a list of all the files in the parent
folder with their unread comment count (can be 0). But this makes sure
that the result is properly cached. In the dav handling.

Signed-off-by: Roeland Jago Douma <roeland@famdouma.nl>

1 files changed, 29 insertions, 14 deletions

diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index f3865c6504f..8df4a84a477 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -581,27 +581,42 @@ class Manager implements ICommentsManager {
 	 * @param int $folderId
 	 * @param IUser $user
 	 * @return array [$fileId => $unreadCount]
+	 *
+	 * @suppress SqlInjectionChecker
 	 */
 	public function getNumberOfUnreadCommentsForFolder($folderId, IUser $user) {
 		$qb = $this->dbConn->getQueryBuilder();
+
 		$query = $qb->select('f.fileid')
 			->addSelect($qb->func()->count('c.id', 'num_ids'))
-			->from('comments', 'c')
-			->innerJoin('c', 'filecache', 'f', $qb->expr()->andX(
-				$qb->expr()->eq('c.object_type', $qb->createNamedParameter('files')),
-				$qb->expr()->eq('f.fileid', $qb->expr()->castColumn('c.object_id', IQueryBuilder::PARAM_INT))
+			->from('filecache', 'f')
+			->leftJoin('f', 'comments', 'c', $qb->expr()->eq(
+				'f.fileid', $qb->expr()->castColumn('c.object_id', IQueryBuilder::PARAM_INT)
 			))
-			->leftJoin('c', 'comments_read_markers', 'm', $qb->expr()->andX(
-				$qb->expr()->eq('m.object_type', $qb->createNamedParameter('files')),
-				$qb->expr()->eq('m.object_id', 'c.object_id'),
-				$qb->expr()->eq('m.user_id', $qb->createNamedParameter($user->getUID()))
+			->leftJoin('c', 'comments_read_markers', 'm', $qb->expr()->eq(
+				'c.object_id', 'm.object_id'
 			))
-			->andWhere($qb->expr()->eq('f.parent', $qb->createNamedParameter($folderId)))
-			->andWhere($qb->expr()->orX(
-				$qb->expr()->gt('c.creation_timestamp', 'marker_datetime'),
-				$qb->expr()->isNull('marker_datetime')
-			))
-			->groupBy('f.fileid');
+			->where(
+				$qb->expr()->andX(
+					$qb->expr()->eq('f.parent', $qb->createNamedParameter($folderId)),
+					$qb->expr()->orX(
+						$qb->expr()->eq('c.object_type', $qb->createNamedParameter('files')),
+						$qb->expr()->isNull('c.object_type')
+					),
+					$qb->expr()->orX(
+						$qb->expr()->eq('m.object_type', $qb->createNamedParameter('files')),
+						$qb->expr()->isNull('m.object_type')
+					),
+					$qb->expr()->orX(
+						$qb->expr()->eq('m.user_id', $qb->createNamedParameter($user->getUID())),
+						$qb->expr()->isNull('m.user_id')
+					),
+					$qb->expr()->orX(
+						$qb->expr()->gt('c.creation_timestamp', 'm.marker_datetime'),
+						$qb->expr()->isNull('m.marker_datetime')
+					)
+				)
+			)->groupBy('f.fileid');
 
 		$resultStatement = $query->execute();