Strict types

Add findAllIndexed function
Add findIdByNameTypeAsArray function
Use AbstractQuery::HYDRATE_SCALAR_COLUMN to get an array of ids

diff --git a/Repository/DanceRepository.php b/Repository/DanceRepository.php
index 24ab2b4452c44c779ef9452c883c5aa460ce1513..e7dd4753dd717e5922921d1c08e0560b87faeb96 100644 (file)
--- a/Repository/DanceRepository.php
+++ b/Repository/DanceRepository.php
@@ -11,18 +11,157 @@
 
 namespace Rapsys\AirBundle\Repository;
 
+use Doctrine\ORM\AbstractQuery;
 use Doctrine\ORM\Query\ResultSetMapping;
 
 /**
  * DanceRepository
  */
-class DanceRepository extends EntityRepository {
+class DanceRepository extends Repository {
+       /**
+        * Find dances indexed by id
+        *
+        * @return array The dances
+        */
+       public function findAllIndexed(): array {
+               //Set the request
+               $req = <<<SQL
+SELECT
+       d.id,
+       d.name,
+       d.type
+FROM RapsysAirBundle:Dance AS d
+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
+               //addScalarResult($sqlColName, $resColName, $type = 'string');
+               $rsm->addEntityResult('RapsysAirBundle:Dance', 'd')
+                       ->addFieldResult('d', 'id', 'id')
+                       ->addFieldResult('d', 'name', 'name')
+                       ->addFieldResult('d', 'type', 'type')
+                       ->addIndexByColumn('d', 'id');
+
+               //Return return
+               return $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->getResult();
+       }
+
+       /**
+        * Find dance choices as array
+        *
+        * @return array The dance choices
+        */
+       public function findChoicesAsArray(): array {
+               //Set the request
+               $req = <<<SQL
+SELECT
+       d.name,
+       GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
+       GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types
+FROM RapsysAirBundle:Dance AS d
+GROUP BY d.name
+ORDER BY d.name
+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
+               //addScalarResult($sqlColName, $resColName, $type = 'string');
+               $rsm->addScalarResult('name', 'name', 'string')
+                       ->addScalarResult('ids', 'ids', 'string')
+                       ->addScalarResult('types', 'types', 'string')
+                       ->addIndexByScalar('name');
+
+               //Get result
+               $result = $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->getArrayResult();
+
+               //Set return
+               $return = [];
+
+               //Iterate on each name
+               foreach($result as $name) {
+                       //Set types
+                       $types = [];
+
+                       //Explode ids
+                       $name['ids'] = explode("\n", $name['ids']);
+
+                       //Explode types
+                       $name['types'] = explode("\n", $name['types']);
+
+                       //Iterate on each type
+                       foreach($name['ids'] as $k => $id) {
+                               //Add to types
+                               $types[$this->translator->trans($name['types'][$k]).' ('.$id.')'] = intval($id);
+                       }
+
+                       //Add to return
+                       $return[$this->translator->trans($name['name'])] = $types;
+               }
+
+               //Return return
+               return $return;
+       }
+
+       /**
+        * Find dances ids by nametype
+        *
+        * @param array $nametype The nametype filter
+        * @return array The dance ids
+        */
+       public function findIdByNameTypeAsArray(array $nametype): array {
+               //Set the request
+               $req = <<<SQL
+SELECT
+       d.id
+FROM RapsysAirBundle:Dance AS d
+WHERE CONCAT_WS(' ', d.name, d.type) IN (:nametype)
+ORDER BY d.name, d.type
+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('nametype', $nametype)
+                       //XXX: instead of array_column on the result
+                       ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
+       }
+
        /**
         * Find dance names as array
         *
         * @return array The dance names
         */
-       public function findNamesAsArray() {
+       public function findNamesAsArray(): array {
                //Set the request
                $req = <<<SQL
 SELECT
@@ -104,7 +243,7 @@ SQL;
         * @param $id The user id
         * @return array The user dances
         */
-       public function findByUserId($userId) {
+       public function findByUserId($userId): array {
                //Set the request
                $req = 'SELECT d.id, d.name, d.type
 FROM RapsysAirBundle:UserDance AS ud