Improve recent file fetching

Fixes #16876

Before we'd just fetch everything from all storages we'd have access to.
Then we'd sort. And filter in php. Now this of course is tricky if a
user shared just a file with you and then has a ton of activity.

Now we try to contruct the prefix path. So that the filtering can happen
right away in the databae.

Now this will make the DB more busy. But it should help overall as in
most cases less queries are needed then etc.

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

2 files changed, 56 insertions, 6 deletions

diff --git a/apps/files_sharing/lib/SharedStorage.php b/apps/files_sharing/lib/SharedStorage.php
index d40e94e36db..7477e5601ff 100644
--- a/apps/files_sharing/lib/SharedStorage.php
+++ b/apps/files_sharing/lib/SharedStorage.php
@@ -505,4 +505,9 @@ class SharedStorage extends \OC\Files\Storage\Wrapper\Jail implements ISharedSto
 	public function setMountOptions(array $options) {
 		$this->mountOptions = $options;
 	}
+
+	public function getUnjailedPath($path) {
+		$this->init();
+		return parent::getUnjailedPath($path);
+	}
 }
diff --git a/lib/private/Files/Node/Folder.php b/lib/private/Files/Node/Folder.php
index d9639e9f1ab..2e061bd4cc6 100644
--- a/lib/private/Files/Node/Folder.php
+++ b/lib/private/Files/Node/Folder.php
@@ -31,6 +31,7 @@
 namespace OC\Files\Node;
 
 use OC\DB\QueryBuilder\Literal;
+use OC\Files\Storage\Wrapper\Jail;
 use OCA\Files_Sharing\SharedStorage;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\Files\Config\ICachedMountInfo;
@@ -438,13 +439,33 @@ class Folder extends Node implements \OCP\Files\Folder {
 		$mountMap = array_combine($storageIds, $mounts);
 		$folderMimetype = $mimetypeLoader->getId(FileInfo::MIMETYPE_FOLDER);
 
+		/*
+		 * Construct an array of the storage id with their prefix path
+		 * This helps us to filter in the final query
+		 */
+		$filters = array_map(function (IMountPoint $mount) {
+			$storage = $mount->getStorage();
+
+			$storageId = $storage->getCache()->getNumericStorageId();
+			$prefix = '';
+
+			if ($storage->instanceOfStorage(Jail::class)) {
+				$prefix = $storage->getUnJailedPath('');
+			}
+
+			return [
+				'storageId' => $storageId,
+				'pathPrefix' => $prefix,
+			];
+		}, $mounts);
+
 		// Search in batches of 500 entries
 		$searchLimit = 500;
 		$results = [];
 		$searchResultCount = 0;
 		$count = 0;
 		do {
-			$searchResult = $this->recentSearch($searchLimit, $offset, $storageIds, $folderMimetype);
+			$searchResult = $this->recentSearch($searchLimit, $offset, $folderMimetype, $filters);
 
 			// Exit condition if there are no more results
 			if (count($searchResult) === 0) {
@@ -466,13 +487,36 @@ class Folder extends Node implements \OCP\Files\Folder {
 		return array_slice($results, 0, $limit);
 	}
 
-	private function recentSearch($limit, $offset, $storageIds, $folderMimetype) {
-		$builder = \OC::$server->getDatabaseConnection()->getQueryBuilder();
+	private function recentSearch($limit, $offset, $folderMimetype, $filters) {
+		$dbconn = \OC::$server->getDatabaseConnection();
+		$builder = $dbconn->getQueryBuilder();
 		$query = $builder
 			->select('f.*')
-			->from('filecache', 'f')
-			->andWhere($builder->expr()->in('f.storage', $builder->createNamedParameter($storageIds, IQueryBuilder::PARAM_INT_ARRAY)))
-			->andWhere($builder->expr()->orX(
+			->from('filecache', 'f');
+
+		/*
+		 * Here is where we construct the filtering.
+		 * Note that this is expensive filtering as it is a lot of like queries.
+		 * However the alternative is we do this filtering and parsing later in php with the risk of looping endlessly
+		 */
+		$storageFilters = $builder->expr()->orX();
+		foreach ($filters as $filter) {
+			$storageFilter = $builder->expr()->andX(
+				$builder->expr()->eq('f.storage', $builder->createNamedParameter($filter['storageId']))
+			);
+
+			if ($filter['pathPrefix'] !== '') {
+				$storageFilter->add(
+					$builder->expr()->like('f.path', $builder->createNamedParameter($dbconn->escapeLikeParameter($filter['pathPrefix']) . '/%'))
+				);
+			}
+
+			$storageFilters->add($storageFilter);
+		}
+
+		$query->andWhere($storageFilters);
+
+		$query->andWhere($builder->expr()->orX(
 			// handle non empty folders separate
 				$builder->expr()->neq('f.mimetype', $builder->createNamedParameter($folderMimetype, IQueryBuilder::PARAM_INT)),
 				$builder->expr()->eq('f.size', new Literal(0))
@@ -482,6 +526,7 @@ class Folder extends Node implements \OCP\Files\Folder {
 			->orderBy('f.mtime', 'DESC')
 			->setMaxResults($limit)
 			->setFirstResult($offset);
+
 		return $query->execute()->fetchAll();
 	}