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 Symfony\Component\Routing\Generator\UrlGeneratorInterface;
  18
  19 use Rapsys\AirBundle\Repository;
  20
  21 /**
  22  * DanceRepository
  23  */
  24 class DanceRepository extends Repository {
  25         /**
  26          * Find dances indexed by id
  27          *
  28          * @return array The dances
  29          */
  30         public function findAllIndexed(): array {
  31                 //Set the request
  32                 $req = <<<SQL
  33 SELECT
  34         d.id,
  35         d.name,
  36         d.type
  37 FROM Rapsys\AirBundle\Entity\Dance AS d
  38 SQL;
  39
  40                 //Replace bundle entity name by table name
  41                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
  42
  43                 //Get result set mapping instance
  44                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  45                 $rsm = new ResultSetMapping();
  46
  47                 //Declare all fields
  48                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  49                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  50                 $rsm->addEntityResult('Rapsys\AirBundle\Entity\Dance', 'd')
  51                         ->addFieldResult('d', 'id', 'id')
  52                         ->addFieldResult('d', 'name', 'name')
  53                         ->addFieldResult('d', 'type', 'type')
  54                         ->addIndexByColumn('d', 'id');
  55
  56                 //Return return
  57                 return $this->_em
  58                         ->createNativeQuery($req, $rsm)
  59                         ->getResult();
  60         }
  61
  62         /**
  63          * Find dance choices as array
  64          *
  65          * @return array The dance choices
  66          */
  67         public function findChoicesAsArray(): array {
  68                 //Set the request
  69                 $req = <<<SQL
  70 SELECT
  71         d.name,
  72         GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
  73         GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types
  74 FROM Rapsys\AirBundle\Entity\Dance AS d
  75 GROUP BY d.name
  76 ORDER BY d.name
  77 SQL;
  78
  79                 //Replace bundle entity name by table name
  80                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
  81
  82                 //Get result set mapping instance
  83                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  84                 $rsm = new ResultSetMapping();
  85
  86                 //Declare all fields
  87                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  88                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  89                 $rsm->addScalarResult('name', 'name', 'string')
  90                         ->addScalarResult('ids', 'ids', 'string')
  91                         ->addScalarResult('types', 'types', 'string')
  92                         ->addIndexByScalar('name');
  93
  94                 //Get result
  95                 $result = $this->_em
  96                         ->createNativeQuery($req, $rsm)
  97                         ->getArrayResult();
  98
  99                 //Set return
 100                 $return = [];
 101
 102                 //Iterate on each name
 103                 foreach($result as $name) {
 104                         //Set types
 105                         $types = [];
 106
 107                         //Explode ids
 108                         $name['ids'] = explode("\n", $name['ids']);
 109
 110                         //Explode types
 111                         $name['types'] = explode("\n", $name['types']);
 112
 113                         //Iterate on each type
 114                         foreach($name['ids'] as $k => $id) {
 115                                 //Add to types
 116                                 $types[$this->translator->trans($name['types'][$k]).' ('.$id.')'] = intval($id);
 117                         }
 118
 119                         //Add to return
 120                         $return[$this->translator->trans($name['name'])] = $types;
 121                 }
 122
 123                 //Return return
 124                 return $return;
 125         }
 126
 127         /**
 128          * Find dances ids by nametype
 129          *
 130          * @param array $nametype The nametype filter
 131          * @return array The dance ids
 132          */
 133         public function findIdByNameTypeAsArray(array $nametype): array {
 134                 //Set the request
 135                 $req = <<<SQL
 136 SELECT
 137         d.id
 138 FROM Rapsys\AirBundle\Entity\Dance AS d
 139 WHERE CONCAT_WS(' ', d.name, d.type) IN (:nametype)
 140 ORDER BY d.name, d.type
 141 SQL;
 142
 143                 //Replace bundle entity name by table name
 144                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 145
 146                 //Get result set mapping instance
 147                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 148                 $rsm = new ResultSetMapping();
 149
 150                 //Declare all fields
 151                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 152                 //XXX: we don't use a result set as we want to translate group and civility
 153                 $rsm->addScalarResult('id', 'id', 'integer');
 154
 155                 //Return result
 156                 return $this->_em
 157                         ->createNativeQuery($req, $rsm)
 158                         ->setParameter('nametype', $nametype)
 159                         //XXX: instead of array_column on the result
 160                         ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
 161         }
 162
 163         /**
 164          * Find dance as array by id
 165          *
 166          * @param int $id The dance id
 167          * @return array The dance data
 168          */
 169         public function findOneByIdAsArray(int $id): ?array {
 170                 //Set the request
 171                 $req = <<<SQL
 172 SELECT
 173         d.id,
 174         d.name,
 175         d.type,
 176         GREATEST(d.created, d.updated) AS modified
 177 FROM Rapsys\AirBundle\Entity\Dance AS d
 178 WHERE d.id = :id
 179 SQL;
 180
 181                 //Replace bundle entity name by table name
 182                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 183
 184                 //Get result set mapping instance
 185                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 186                 $rsm = new ResultSetMapping();
 187
 188                 //Declare all fields
 189                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 190                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 191                 $rsm->addScalarResult('id', 'id', 'integer')
 192                         ->addScalarResult('name', 'name', 'string')
 193                         ->addScalarResult('type', 'type', 'string')
 194                         ->addScalarResult('modified', 'modified', 'datetime')
 195                         ->addIndexByScalar('id');
 196
 197                 //Get result
 198                 $result = $this->_em
 199                         ->createNativeQuery($req, $rsm)
 200                         ->setParameter('id', $id)
 201                         ->getOneOrNullResult();
 202
 203                 //Without result
 204                 if ($result === null) {
 205                         //Return result
 206                         return $result;
 207                 }
 208
 209                 //Set alternates
 210                 $result['alternates'] = [];
 211
 212                 //Set route
 213                 $route = 'rapsysair_dance_view';
 214
 215                 //Set route params
 216                 $routeParams = ['id' => $id];
 217
 218                 //Iterate on each languages
 219                 foreach($this->languages as $languageId => $language) {
 220                         //Without current locale
 221                         if ($languageId !== $this->locale) {
 222                                 //Set titles
 223                                 $titles = [];
 224
 225                                 //Set route params locale
 226                                 $routeParams['_locale'] = $languageId;
 227
 228                                 //Set route params name
 229                                 $routeParams['name'] = $this->slugger->slug($this->translator->trans($result['name'], [], null, $languageId));
 230
 231                                 //Set route params type
 232                                 $routeParams['type'] = $this->slugger->slug($this->translator->trans($result['type'], [], null, $languageId));
 233
 234                                 //Iterate on each locales
 235                                 foreach(array_keys($this->languages) as $other) {
 236                                         //Without other locale
 237                                         if ($other !== $languageId) {
 238                                                 //Set other locale title
 239                                                 $titles[$other] = $this->translator->trans($language, [], null, $other);
 240                                         }
 241                                 }
 242
 243                                 //Add alternates locale
 244                                 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
 245                                         'absolute' => $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
 246                                         'relative' => $this->router->generate($route, $routeParams),
 247                                         'title' => implode('/', $titles),
 248                                         'translated' => $this->translator->trans($language, [], null, $languageId)
 249                                 ];
 250                         }
 251                 }
 252
 253                 //Return result
 254                 return [
 255                         'id' => $result['id'],
 256                         'name' => $name = $this->translator->trans($result['name']),
 257                         'type' => $type = $this->translator->trans($result['type']),
 258                         'slug' => [
 259                                 'name' => $sname = $this->slugger->slug($name),
 260                                 'type' => $stype = $this->slugger->slug($type)
 261                         ],
 262                         'modified' => $result['modified'],
 263                         //XXX: Useless ???
 264                         'link' => $this->router->generate($route, ['_locale' => $this->locale, 'name' => $sname, 'type' => $stype]+$routeParams),
 265                         'alternates' => $result['alternates']
 266                 ];
 267         }
 268
 269         /**
 270          * Find dance names as array
 271          *
 272          * @return array The dance names
 273          */
 274         public function findNamesAsArray(): array {
 275                 //Set the request
 276                 $req = <<<SQL
 277 SELECT
 278         d.name,
 279         GROUP_CONCAT(d.id ORDER BY d.id SEPARATOR "\\n") AS ids,
 280         GROUP_CONCAT(d.type ORDER BY d.id SEPARATOR "\\n") AS types,
 281         MAX(d.updated) AS modified
 282 FROM Rapsys\AirBundle\Entity\Dance AS d
 283 GROUP BY d.name
 284 ORDER BY d.name
 285 SQL;
 286
 287                 //Replace bundle entity name by table name
 288                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 289
 290                 //Get result set mapping instance
 291                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 292                 $rsm = new ResultSetMapping();
 293
 294                 //Declare all fields
 295                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 296                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 297                 $rsm->addScalarResult('name', 'name', 'string')
 298                         ->addScalarResult('ids', 'ids', 'string')
 299                         ->addScalarResult('types', 'types', 'string')
 300                         ->addScalarResult('modified', 'modified', 'datetime')
 301                         ->addIndexByScalar('name');
 302
 303                 //Get result
 304                 $result = $this->_em
 305                         ->createNativeQuery($req, $rsm)
 306                         ->getArrayResult();
 307
 308                 //Set return
 309                 $return = [];
 310
 311                 //Iterate on each name
 312                 foreach($result as $name) {
 313                         //Set name slug
 314                         $slug = $this->slugger->slug($tname = $this->translator->trans($name['name']));
 315
 316                         //Set types
 317                         $types = [];
 318
 319                         //Explode ids
 320                         $name['ids'] = explode("\n", $name['ids']);
 321
 322                         //Explode types
 323                         $name['types'] = explode("\n", $name['types']);
 324
 325                         //Iterate on each type
 326                         foreach($name['ids'] as $k => $id) {
 327                                 //Add to types
 328                                 $types[$this->slugger->short($name['types'][$k])] = [
 329                                         'id' => $id,
 330                                         'type' => $type = $this->translator->trans($name['types'][$k]),
 331                                         'slug' => $stype = $this->slugger->slug($type),
 332                                         'link' => $this->router->generate('rapsysair_dance_view', ['id' => $id, 'name' => $slug, 'type' => $stype])
 333                                 ];
 334                         }
 335
 336                         //Add to return
 337                         $return[$sname = $this->slugger->short($name['name'])] = [
 338                                 'name' => $tname,
 339                                 'slug' => $slug,
 340                                 'link' => $this->router->generate('rapsysair_dance_name', ['name' => $sname, 'dance' => $slug]),
 341                                 'types' => $types,
 342                                 'modified' => $name['modified']
 343                         ];
 344                 }
 345
 346                 //Return return
 347                 return $return;
 348         }
 349
 350         /**
 351          * Find dances by user id
 352          *
 353          * @param $id The user id
 354          * @return array The user dances
 355          */
 356         public function findByUserId($userId): array {
 357                 //Set the request
 358                 $req = 'SELECT d.id, d.name, d.type
 359 FROM Rapsys\AirBundle\Entity\UserDance AS ud
 360 JOIN Rapsys\AirBundle\Entity\Dance AS d ON (d.id = ud.dance_id)
 361 WHERE ud.user_id = :uid';
 362
 363                 //Replace bundle entity name by table name
 364                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 365
 366                 //Get result set mapping instance
 367                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 368                 $rsm = new ResultSetMapping();
 369
 370                 //Declare result set for our request
 371                 $rsm->addEntityResult('Rapsys\AirBundle\Entity\Dance', 'd');
 372                 $rsm->addFieldResult('d', 'id', 'id');
 373                 $rsm->addFieldResult('d', 'name', 'name');
 374                 $rsm->addFieldResult('d', 'type', 'type');
 375
 376                 //Send result
 377                 return $this->_em
 378                         ->createNativeQuery($req, $rsm)
 379                         ->setParameter('uid', $userId)
 380                         ->getResult();
 381         }
 382 }