From 849cc2e2bdd1b0c01d32e79d2d8d2fe862e89cd1 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Rapha=C3=ABl=20Gertz?= <git@rapsys.eu>
Date: Wed, 21 Feb 2024 11:49:24 +0100
Subject: [PATCH] Rename EntityRepository to Repository Rename
 findIndexByGroupPseudonym function to findChoicesAsArray Move filtering of
 groupless users and simple user to sql request Trim user title Add function
 findIdByPseudonymAsArray Use AbstractQuery::HYDRATE_SCALAR_COLUMN to get an
 array of ids

---
 Repository/UserRepository.php | 67 +++++++++++++++++++++++++++++------
 1 file changed, 57 insertions(+), 10 deletions(-)

diff --git a/Repository/UserRepository.php b/Repository/UserRepository.php
index 9e869e1..5f5dd50 100644
--- a/Repository/UserRepository.php
+++ b/Repository/UserRepository.php
@@ -11,19 +11,20 @@
 
 namespace Rapsys\AirBundle\Repository;
 
+use Doctrine\ORM\AbstractQuery;
 use Doctrine\ORM\Query\ResultSetMapping;
 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
 
 /**
  * UserRepository
  */
-class UserRepository extends EntityRepository {
+class UserRepository extends Repository {
 	/**
 	 * Find users with translated highest group and civility
 	 *
 	 * @return array The user ids keyed by group and pseudonym
 	 */
-	public function findIndexByGroupPseudonym(): array {
+	public function findChoicesAsArray(): array {
 		//Set the request
 		$req =<<<SQL
 SELECT
@@ -38,9 +39,10 @@ FROM (
 		g.id AS g_id,
 		g.title AS g_title
 	FROM RapsysAirBundle:User AS u
-	LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-	LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
-	ORDER BY g.id DESC
+	JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
+	JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+	WHERE g.title <> 'User'
+	ORDER BY g.id DESC, u.pseudonym ASC
 	LIMIT 0, :limit
 ) AS a
 GROUP BY a.id
@@ -74,10 +76,11 @@ SQL;
 		//Process result
 		foreach($res as $data) {
 			//Without group or simple user
-			if (empty($data['g_title']) || $data['g_title'] == 'User') {
-				//Skip it
-				continue;
-			}
+			#XXX: moved in sql by removing LEFT JOIN and excluding user group
+			#if (empty($data['g_title']) || $data['g_title'] == 'User') {
+			#	//Skip it
+			#	continue;
+			#}
 
 			//Get translated group
 			$group = $this->translator->trans($data['g_title']);
@@ -89,13 +92,57 @@ SQL;
 
 			//Set data
 			//XXX: ChoiceType use display string as key
-			$ret[$group][$data['pseudonym']] = $data['id'];
+			$ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);
 		}
 
 		//Send result
 		return $ret;
 	}
 
+	/**
+	 * Find user ids by pseudonym
+	 *
+	 * @param array $pseudonym The pseudonym filter
+	 * @return array The user ids
+	 */
+	public function findIdByPseudonymAsArray(array $pseudonym): array {
+		//Set the request
+		$req =<<<SQL
+SELECT
+	a.id
+FROM (
+	SELECT
+		u.id
+	FROM RapsysAirBundle:User AS u
+	LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
+	WHERE u.pseudonym IN (:pseudonym)
+	ORDER BY gu.group_id DESC, u.pseudonym ASC
+	LIMIT 0, :limit
+) AS a
+GROUP BY a.id
+ORDER BY NULL
+SQL;
+
+		//Replace bundle entity name by table name
+		$req = str_replace($this->tableKeys, $this->tableValues, $req);
+
+		//Get result set mapping instance
+		//XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+		$rsm = new ResultSetMapping();
+
+		//Declare all fields
+		//XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+		//XXX: we don't use a result set as we want to translate group and civility
+		$rsm->addScalarResult('id', 'id', 'integer');
+
+		//Return result
+		return $this->_em
+			->createNativeQuery($req, $rsm)
+			->setParameter('pseudonym', $pseudonym)
+			//XXX: instead of array_column on the result
+			->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
+	}
+
 	/**
 	 * Find applicant by session id
 	 *
-- 
2.41.1