Raphaël G. Git Repositories - airbundle/blob - Repository/DanceRepository.php

   1 <?php declare(strict_types=1);
   2
   3 /*
   4  * This file is part of the Rapsys AirBundle package.
   5  *
   6  * (c) Raphaël Gertz <symfony@rapsys.eu>
   7  *
   8  * For the full copyright and license information, please view the LICENSE
   9  * file that was distributed with this source code.
  10  */
  11
  12 namespace Rapsys\AirBundle\Repository;
  13
  14 use Doctrine\ORM\AbstractQuery;
  15 use Doctrine\ORM\Query\ResultSetMapping;
  16
  17 use Rapsys\AirBundle\Repository;
  18
  19 /**
  20  * DanceRepository
  21  */
  22 class DanceRepository extends Repository {
  23         /**
  24          * Find dances indexed by id
  25          *
  26          * @return array The dances
  27          */
  28         public function findAllIndexed(): array {
  29                 //Set the request
  30                 $req = <<<SQL
  31 SELECT
  32         d.id,
  33         d.name,
  34         d.type
  35 FROM RapsysAirBundle:Dance AS d
  36 SQL;
  37
  38                 //Replace bundle entity name by table name
  39                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
  40
  41                 //Get result set mapping instance
  42                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  43                 $rsm = new ResultSetMapping();
  44
  45                 //Declare all fields
  46                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  47                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  48                 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd')
  49                         ->addFieldResult('d', 'id', 'id')
  50                         ->addFieldResult('d', 'name', 'name')
  51                         ->addFieldResult('d', 'type', 'type')
  52                         ->addIndexByColumn('d', 'id');
  53
  54                 //Return return
  55                 return $this->_em
  56                         ->createNativeQuery($req, $rsm)
  57                         ->getResult();
  58         }
  59
  60         /**
  61          * Find dance choices as array
  62          *
  63          * @return array The dance choices
  64          */
  65         public function findChoicesAsArray(): array {
  66                 //Set the request
  67                 $req = <<<SQL
  68 SELECT
  69         d.name,
  70         GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
  71         GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types
  72 FROM RapsysAirBundle:Dance AS d
  73 GROUP BY d.name
  74 ORDER BY d.name
  75 SQL;
  76
  77                 //Replace bundle entity name by table name
  78                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
  79
  80                 //Get result set mapping instance
  81                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  82                 $rsm = new ResultSetMapping();
  83
  84                 //Declare all fields
  85                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  86                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  87                 $rsm->addScalarResult('name', 'name', 'string')
  88                         ->addScalarResult('ids', 'ids', 'string')
  89                         ->addScalarResult('types', 'types', 'string')
  90                         ->addIndexByScalar('name');
  91
  92                 //Get result
  93                 $result = $this->_em
  94                         ->createNativeQuery($req, $rsm)
  95                         ->getArrayResult();
  96
  97                 //Set return
  98                 $return = [];
  99
 100                 //Iterate on each name
 101                 foreach($result as $name) {
 102                         //Set types
 103                         $types = [];
 104
 105                         //Explode ids
 106                         $name['ids'] = explode("\n", $name['ids']);
 107
 108                         //Explode types
 109                         $name['types'] = explode("\n", $name['types']);
 110
 111                         //Iterate on each type
 112                         foreach($name['ids'] as $k => $id) {
 113                                 //Add to types
 114                                 $types[$this->translator->trans($name['types'][$k]).' ('.$id.')'] = intval($id);
 115                         }
 116
 117                         //Add to return
 118                         $return[$this->translator->trans($name['name'])] = $types;
 119                 }
 120
 121                 //Return return
 122                 return $return;
 123         }
 124
 125         /**
 126          * Find dances ids by nametype
 127          *
 128          * @param array $nametype The nametype filter
 129          * @return array The dance ids
 130          */
 131         public function findIdByNameTypeAsArray(array $nametype): array {
 132                 //Set the request
 133                 $req = <<<SQL
 134 SELECT
 135         d.id
 136 FROM RapsysAirBundle:Dance AS d
 137 WHERE CONCAT_WS(' ', d.name, d.type) IN (:nametype)
 138 ORDER BY d.name, d.type
 139 SQL;
 140
 141                 //Replace bundle entity name by table name
 142                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 143
 144                 //Get result set mapping instance
 145                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 146                 $rsm = new ResultSetMapping();
 147
 148                 //Declare all fields
 149                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 150                 //XXX: we don't use a result set as we want to translate group and civility
 151                 $rsm->addScalarResult('id', 'id', 'integer');
 152
 153                 //Return result
 154                 return $this->_em
 155                         ->createNativeQuery($req, $rsm)
 156                         ->setParameter('nametype', $nametype)
 157                         //XXX: instead of array_column on the result
 158                         ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
 159         }
 160
 161         /**
 162          * Find dance names as array
 163          *
 164          * @return array The dance names
 165          */
 166         public function findNamesAsArray(): array {
 167                 //Set the request
 168                 $req = <<<SQL
 169 SELECT
 170         d.name,
 171         GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
 172         GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types,
 173         MAX(d.updated) AS modified
 174 FROM RapsysAirBundle:Dance AS d
 175 GROUP BY d.name
 176 ORDER BY d.name
 177 SQL;
 178
 179                 //Replace bundle entity name by table name
 180                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 181
 182                 //Get result set mapping instance
 183                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 184                 $rsm = new ResultSetMapping();
 185
 186                 //Declare all fields
 187                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 188                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 189                 $rsm->addScalarResult('name', 'name', 'string')
 190                         ->addScalarResult('ids', 'ids', 'string')
 191                         ->addScalarResult('types', 'types', 'string')
 192                         ->addScalarResult('modified', 'modified', 'datetime')
 193                         ->addIndexByScalar('name');
 194
 195                 //Get result
 196                 $result = $this->_em
 197                         ->createNativeQuery($req, $rsm)
 198                         ->getArrayResult();
 199
 200                 //Set return
 201                 $return = [];
 202
 203                 //Iterate on each name
 204                 foreach($result as $name) {
 205                         //Set name slug
 206                         $slug = $this->slugger->slug($tname = $this->translator->trans($name['name']));
 207
 208                         //Set types
 209                         $types = [];
 210
 211                         //Explode ids
 212                         $name['ids'] = explode("\n", $name['ids']);
 213
 214                         //Explode types
 215                         $name['types'] = explode("\n", $name['types']);
 216
 217                         //Iterate on each type
 218                         foreach($name['ids'] as $k => $id) {
 219                                 //Add to types
 220                                 $types[$this->slugger->short($name['types'][$k])] = [
 221                                         'id' => $id,
 222                                         'type' => $type = $this->translator->trans($name['types'][$k]),
 223                                         'slug' => $stype = $this->slugger->slug($type),
 224                                         'link' => $this->router->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $slug, 'type' => $stype])
 225                                 ];
 226                         }
 227
 228                         //Add to return
 229                         $return[$sname = $this->slugger->short($name['name'])] = [
 230                                 'name' => $tname,
 231                                 'slug' => $slug,
 232                                 'link' => $this->router->generate('rapsys_air_dance_name', ['name' => $sname, 'dance' => $slug]),
 233                                 'types' => $types,
 234                                 'modified' => $name['modified']
 235                         ];
 236                 }
 237
 238                 //Return return
 239                 return $return;
 240         }
 241
 242         /**
 243          * Find dances by user id
 244          *
 245          * @param $id The user id
 246          * @return array The user dances
 247          */
 248         public function findByUserId($userId): array {
 249                 //Set the request
 250                 $req = 'SELECT d.id, d.name, d.type
 251 FROM RapsysAirBundle:UserDance AS ud
 252 JOIN RapsysAirBundle:Dance AS d ON (d.id = ud.dance_id)
 253 WHERE ud.user_id = :uid';
 254
 255                 //Replace bundle entity name by table name
 256                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 257
 258                 //Get result set mapping instance
 259                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 260                 $rsm = new ResultSetMapping();
 261
 262                 //Declare result set for our request
 263                 $rsm->addEntityResult('RapsysAirBundle:Dance', 'd');
 264                 $rsm->addFieldResult('d', 'id', 'id');
 265                 $rsm->addFieldResult('d', 'name', 'name');
 266                 $rsm->addFieldResult('d', 'type', 'type');
 267
 268                 //Send result
 269                 return $this->_em
 270                         ->createNativeQuery($req, $rsm)
 271                         ->setParameter('uid', $userId)
 272                         ->getResult();
 273         }
 274 }