Merge pull request #9129 from nextcloud/feature/noid/proper-comments-offset

Add proper comment offset support

2 files changed, 135 insertions, 0 deletions

diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index 2b43a27b7ac..9393cce334d 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -377,6 +377,123 @@ class Manager implements ICommentsManager {
 	}
 
 	/**
+	 * @param string $objectType the object type, e.g. 'files'
+	 * @param string $objectId the id of the object
+	 * @param int $lastKnownCommentId the last known comment (will be used as offset)
+	 * @param string $sortDirection direction of the comments (`asc` or `desc`)
+	 * @param int $limit optional, number of maximum comments to be returned. if
+	 * set to 0, all comments are returned.
+	 * @return IComment[]
+	 * @return array
+	 */
+	public function getForObjectSince(
+		string $objectType,
+		string $objectId,
+		int $lastKnownCommentId,
+		string $sortDirection = 'asc',
+		int $limit = 30
+	): array {
+		$comments = [];
+
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select('*')
+			->from('comments')
+			->where($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
+			->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)))
+			->orderBy('creation_timestamp', $sortDirection === 'desc' ? 'DESC' : 'ASC')
+			->addOrderBy('id', $sortDirection === 'desc' ? 'DESC' : 'ASC');
+
+		if ($limit > 0) {
+			$query->setMaxResults($limit);
+		}
+
+		$lastKnownComment = $this->getLastKnownComment(
+			$objectType,
+			$objectId,
+			$lastKnownCommentId
+		);
+		if ($lastKnownComment instanceof IComment) {
+			$lastKnownCommentDateTime = $lastKnownComment->getCreationDateTime();
+			if ($sortDirection === 'desc') {
+				$query->andWhere(
+					$query->expr()->orX(
+						$query->expr()->lt(
+							'creation_timestamp',
+							$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
+							IQueryBuilder::PARAM_DATE
+						),
+						$query->expr()->andX(
+							$query->expr()->eq(
+								'creation_timestamp',
+								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
+								IQueryBuilder::PARAM_DATE
+							),
+							$query->expr()->lt('id', $query->createNamedParameter($lastKnownCommentId))
+						)
+					)
+				);
+			} else {
+				$query->andWhere(
+					$query->expr()->orX(
+						$query->expr()->gt(
+							'creation_timestamp',
+							$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
+							IQueryBuilder::PARAM_DATE
+						),
+						$query->expr()->andX(
+							$query->expr()->eq(
+								'creation_timestamp',
+								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
+								IQueryBuilder::PARAM_DATE
+							),
+							$query->expr()->gt('id', $query->createNamedParameter($lastKnownCommentId))
+						)
+					)
+				);
+			}
+		}
+
+		$resultStatement = $query->execute();
+		while ($data = $resultStatement->fetch()) {
+			$comment = new Comment($this->normalizeDatabaseData($data));
+			$this->cache($comment);
+			$comments[] = $comment;
+		}
+		$resultStatement->closeCursor();
+
+		return $comments;
+	}
+
+	/**
+	 * @param string $objectType the object type, e.g. 'files'
+	 * @param string $objectId the id of the object
+	 * @param int $id the comment to look for
+	 * @return Comment|null
+	 */
+	protected function getLastKnownComment(string $objectType,
+										   string $objectId,
+										   int $id) {
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select('*')
+			->from('comments')
+			->where($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
+			->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)))
+			->andWhere($query->expr()->eq('id', $query->createNamedParameter($id, IQueryBuilder::PARAM_INT)));
+
+		$result = $query->execute();
+		$row = $result->fetch();
+		$result->closeCursor();
+
+		if ($row) {
+			$comment = new Comment($this->normalizeDatabaseData($row));
+			$this->cache($comment);
+			return $comment;
+		}
+
+		return null;
+	}
+
+	/**
 	 * @param $objectType string the object type, e.g. 'files'
 	 * @param $objectId string the id of the object
 	 * @param \DateTime $notOlderThan optional, timestamp of the oldest comments
diff --git a/lib/public/Comments/ICommentsManager.php b/lib/public/Comments/ICommentsManager.php
index bd9c4ff5ba6..b3ed176b3b5 100644
--- a/lib/public/Comments/ICommentsManager.php
+++ b/lib/public/Comments/ICommentsManager.php
@@ -121,6 +121,24 @@ interface ICommentsManager {
 	);
 
 	/**
+	 * @param string $objectType the object type, e.g. 'files'
+	 * @param string $objectId the id of the object
+	 * @param int $lastKnownCommentId the last known comment (will be used as offset)
+	 * @param string $sortDirection direction of the comments (`asc` or `desc`)
+	 * @param int $limit optional, number of maximum comments to be returned. if
+	 * set to 0, all comments are returned.
+	 * @return IComment[]
+	 * @since 14.0.0
+	 */
+	public function getForObjectSince(
+		string $objectType,
+		string $objectId,
+		int $lastKnownCommentId,
+		string $sortDirection = 'asc',
+		int $limit = 30
+	): array;
+
+	/**
 	 * @param $objectType string the object type, e.g. 'files'
 	 * @param $objectId string the id of the object
 	 * @param \DateTime|null $notOlderThan optional, timestamp of the oldest comments