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