Optimize retrieving display name when searching for users in a group

This is recurrent scenario that we are searching for users and then for
each users we fetch the displayName. This is inefficient, so instead try
to do one query to fetch everything (e.g. Database backend) or use the
already existing DisplayNameCache helper.

Signed-off-by: Carl Schwan <carl@carlschwan.eu>

2 files changed, 58 insertions, 11 deletions

diff --git a/lib/private/Group/Database.php b/lib/private/Group/Database.php
index 5f17477db77..bfc95c574e2 100644
--- a/lib/private/Group/Database.php
+++ b/lib/private/Group/Database.php
@@ -43,6 +43,9 @@ use OCP\Group\Backend\IRemoveFromGroupBackend;
 use OCP\Group\Backend\ISetDisplayNameBackend;
 use OCP\Group\Backend\INamedBackend;
 use OCP\IDBConnection;
+use OCP\IUserManager;
+use OC\User\LazyUser;
+use OC\User\DisplayNameCache;
 
 /**
  * Class for group management in a SQL Database (e.g. MySQL, SQLite)
@@ -376,6 +379,55 @@ class Database extends ABackend implements
 		return $users;
 	}
 
+	public function searchInGroup(string $gid, string $search = '', int $limit = -1, int $offset = 0): array {
+		$this->fixDI();
+
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select('g.uid', 'u.displayname');
+
+		$query->from('group_user', 'g')
+			->where($query->expr()->eq('gid', $query->createNamedParameter($gid)))
+			->orderBy('g.uid', 'ASC');
+
+		$query->leftJoin('g', 'users', 'u', $query->expr()->eq('g.uid', 'u.uid'))
+
+		if ($search !== '') {
+			$query->leftJoin('u', 'preferences', 'p', $query->expr()->andX(
+				$query->expr()->eq('p.userid', 'u.uid'),
+				$query->expr()->eq('p.appid', $query->expr()->literal('settings')),
+				$query->expr()->eq('p.configkey', $query->expr()->literal('email'))
+			))
+				// sqlite doesn't like re-using a single named parameter here
+				->andWhere(
+					$query->expr()->orX(
+						$query->expr()->ilike('g.uid', $query->createNamedParameter('%' . $this->dbConn->escapeLikeParameter($search) . '%')),
+						$query->expr()->ilike('u.displayname', $query->createNamedParameter('%' . $this->dbConn->escapeLikeParameter($search) . '%')),
+						$query->expr()->ilike('p.configvalue', $query->createNamedParameter('%' . $this->dbConn->escapeLikeParameter($search) . '%'))
+					)
+				)
+				->orderBy('u.uid_lower', 'ASC');
+		}
+
+		if ($limit !== -1) {
+			$query->setMaxResults($limit);
+		}
+		if ($offset !== 0) {
+			$query->setFirstResult($offset);
+		}
+
+		$result = $query->executeQuery();
+
+		$users = [];
+		$userManager = \OCP\Server::get(IUserManager::class);
+		$displayNameCache = \OCP\Server::get(DisplayNameCache::class);
+		while ($row = $result->fetch()) {
+			$users[$row['uid']] = new LazyUser($row['uid'], $displayNameCache, $userManager, $row['displayname'] ?? null);
+		}
+		$result->closeCursor();
+
+		return $users;
+	}
+
 	/**
 	 * get the number of all users matching the search string in a group
 	 * @param string $gid
diff --git a/lib/private/Group/Group.php b/lib/private/Group/Group.php
index cca179bfe19..b2903bcdb26 100644
--- a/lib/private/Group/Group.php
+++ b/lib/private/Group/Group.php
@@ -242,18 +242,13 @@ class Group implements IGroup {
 	}
 
 	/**
-	 * search for users in the group by userid
-	 *
-	 * @param string $search
-	 * @param int $limit
-	 * @param int $offset
-	 * @return \OC\User\User[]
+	 * Search for users in the group by userid or display name
+	 * @return IUser[]
 	 */
-	public function searchUsers($search, $limit = null, $offset = null) {
+	public function searchUsers(string $search, ?int $limit = null, ?int $offset = null): array {
 		$users = [];
 		foreach ($this->backends as $backend) {
-			$userIds = $backend->usersInGroup($this->gid, $search, $limit, $offset);
-			$users += $this->getVerifiedUsers($userIds);
+			$users = array_merge($users, $backend->searchInGroup($this->gid, $search, $limit ?? -1, $offset ?? 0));
 			if (!is_null($limit) and $limit <= 0) {
 				return $users;
 			}
@@ -309,12 +304,12 @@ class Group implements IGroup {
 	 * @param int $limit
 	 * @param int $offset
 	 * @return \OC\User\User[]
+	 * @deprecated 25.0.0 Use searchUsers instead (same implementation)
 	 */
 	public function searchDisplayName($search, $limit = null, $offset = null) {
 		$users = [];
 		foreach ($this->backends as $backend) {
-			$userIds = $backend->usersInGroup($this->gid, $search, $limit, $offset);
-			$users = $this->getVerifiedUsers($userIds);
+			$users = array_merge($users, $backend->searchInGroup($this->gid, $search, $limit ?? -1, $offset ?? 0));
 			if (!is_null($limit) and $limit <= 0) {
 				return array_values($users);
 			}