Add proper comment offset support

The offset is based on the last known comment instead of limit-offset,
so new comments don't mess up requests which get the history of an object-

Signed-off-by: Joas Schilling <coding@schilljs.com>

1 files changed, 115 insertions, 0 deletions

diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index 2b43a27b7ac..37b5f033097 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -377,6 +377,121 @@ class Manager implements ICommentsManager {
 	}
 
 	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param int $lastKnownCommentId
+	 * @param string $sortDirection
+	 * @param int $limit
+	 * @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
+	 * @param string $objectId
+	 * @param int $id
+	 * @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