Raphaël G. Git Repositories - treebundle/blob - Repository/AlbumRepository.php

   1 <?php declare(strict_types=1);
   2
   3 /*
   4  * This file is part of the Rapsys TreeBundle 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\TreeBundle\Repository;
  13
  14 use Doctrine\ORM\Query\ResultSetMapping;
  15
  16 use Rapsys\TreeBundle\Repository;
  17
  18 /**
  19  * Album repository
  20  */
  21 class AlbumRepository extends Repository {
  22         /**
  23          * Find album count as int
  24          *
  25          * @param ?integer $id The user id
  26          * @return integer The albums count
  27          */
  28         public function findCountAsInt(?int $id): int {
  29                 //Set user sql
  30                 $userSql = <<<SQL
  31 a.user_id = :id
  32 SQL;
  33
  34                 //With null id
  35                 if ($id === null) {
  36                         //Set user sql
  37                         $userSql = <<<SQL
  38 a.user_id IS NULL
  39 SQL;
  40                 }
  41
  42                 //Set the request
  43                 $req = <<<SQL
  44 SELECT COUNT(DISTINCT a.album_id) AS count
  45 FROM Rapsys\TreeBundle\Entity\Element AS a
  46 WHERE $userSql
  47 SQL;
  48
  49                 //Get result set mapping instance
  50                 $req = $this->replace($req);
  51
  52                 //Get result set mapping instance
  53                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  54                 $rsm = new ResultSetMapping();
  55
  56                 //Declare all fields
  57                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  58                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  59                 $rsm->addScalarResult('count', 'count', 'integer');
  60
  61                 //Get result
  62                 return $this->_em
  63                         ->createNativeQuery($req, $rsm)
  64                         ->setParameter('id', $id)
  65                         ->getSingleScalarResult();
  66         }
  67
  68         /**
  69          * Find albums as array
  70          *
  71          * @param ?integer $id The user id
  72          * @param integer $page The page
  73          * @param integer $count The count
  74          * @return array The albums array
  75          */
  76         public function findAllAsArray(?int $id, int $page, int $count): array {
  77                 //Set user sql
  78                 $userSql = <<<SQL
  79 e.user_id = :id
  80 SQL;
  81
  82                 //With null id
  83                 if ($id === null) {
  84                         //Set user sql
  85                         $userSql = <<<SQL
  86 e.user_id IS NULL
  87 SQL;
  88                 }
  89
  90                 //Set the request
  91                 $req = <<<SQL
  92 SELECT
  93         a.id,
  94         a.path,
  95         GROUP_CONCAT(e.id ORDER BY e.id SEPARATOR "\\n") AS s_ids,
  96         GROUP_CONCAT(IFNULL(e.path, '/') ORDER BY e.id SEPARATOR "\\n") AS s_paths,
  97         a.slug,
  98         GREATEST(a.created, e.created) AS created,
  99         GREATEST(a.updated, e.updated) AS updated,
 100         GREATEST(a.created, e.created, a.updated, e.updated) AS modified
 101 FROM Rapsys\TreeBundle\Entity\Album AS a
 102 JOIN Rapsys\TreeBundle\Entity\Element AS e ON (e.album_id = a.id AND {$userSql})
 103 GROUP BY a.id
 104 ORDER BY updated, created DESC, a.id
 105 LIMIT :offset, :count
 106 SQL;
 107
 108                 //Replace bundle entity name by table name
 109                 $req = $this->replace($req);
 110
 111                 //Get result set mapping instance
 112                 $rsm = new ResultSetMapping();
 113
 114                 //Declare all fields
 115                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 116                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 117                 $rsm->addScalarResult('id', 'id', 'integer')
 118                         ->addScalarResult('path', 'path', 'string')
 119                         ->addScalarResult('slug', 'slug', 'string')
 120                         ->addScalarResult('s_ids', 's_ids', 'string')
 121                         ->addScalarResult('s_paths', 's_paths', 'string')
 122                         ->addScalarResult('created', 'created', 'datetime')
 123                         ->addScalarResult('updated', 'updated', 'datetime')
 124                         ->addScalarResult('modified', 'modified', 'datetime')
 125                         ->addIndexByScalar('id');
 126
 127                 //Get result
 128                 $result = $this->_em
 129                         ->createNativeQuery($req, $rsm)
 130                         ->setParameter('offset', $page * $count)
 131                         ->setParameter('count', $count)
 132                         ->setParameter('id', $id)
 133                         ->getArrayResult();
 134
 135                 //Set return
 136                 $return = [];
 137
 138                 //Iterate on each city
 139                 foreach($result as $data) {
 140                         //Add to return
 141                         $return[$data['id']] = [
 142                                 'id' => $id = $data['id'],
 143                                 'slug' => $slug = $data['slug'],
 144                                 'path' => $data['path'],
 145                                 'created' => $data['created'],
 146                                 'updated' => $data['updated'],
 147                                 'modified' => $data['modified'],
 148                                 'link' => $this->router->generate('rapsystree_album', ['id' => $id, 'slug' => $slug, 'path' => '/']),
 149                                 'elements' => []
 150                         ];
 151
 152                         //Explode element ids
 153                         $data['s_ids'] = explode("\n", $data['s_ids']);
 154
 155                         //Explode element paths
 156                         $data['s_paths'] = explode("\n", $data['s_paths']);
 157
 158                         foreach($data['s_ids'] as $s => $id) {
 159                                 $return[$data['id']]['elements'][$id] = [
 160                                         'id' => $id,
 161                                         'path' => $path = $data['s_paths'][$s],
 162                                         'link' => $this->router->generate('rapsystree_element', ['id' => $id, 'path' => $path]),
 163                                 ];
 164                         }
 165                 }
 166
 167                 //Return return
 168                 return $return;
 169         }
 170 }