Raphaël G. Git Repositories - blogbundle/blob - Repository/ArticleRepository.php

   1 <?php declare(strict_types=1);
   2
   3 /*
   4  * This file is part of the Rapsys BlogBundle 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\BlogBundle\Repository;
  13
  14 use Doctrine\ORM\Query\ResultSetMapping;
  15
  16 /**
  17  * ArticleRepository
  18  *
  19  * This class was generated by the Doctrine ORM. Add your own custom
  20  * repository methods below.
  21  */
  22 class ArticleRepository extends EntityRepository {
  23         /**
  24          * Find article count as int
  25          *
  26          * @return integer The articles count
  27          */
  28         public function findCountAsInt(): int {
  29                 //Set the request
  30                 $req = <<<SQL
  31 SELECT COUNT(at.article_id) AS count
  32 FROM RapsysBlogBundle:ArticleTranslation AS at
  33 WHERE at.locale = :locale
  34 SQL;
  35
  36                 //Get result set mapping instance
  37                 $req = $this->replace($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->addScalarResult('count', 'count', 'integer');
  47
  48                 //Get result
  49                 return $this->_em
  50                         ->createNativeQuery($req, $rsm)
  51                         ->getSingleScalarResult();
  52         }
  53
  54         /**
  55          * Find articles as array
  56          *
  57          * @param integer $page The page
  58          * @param integer $count The count
  59          * @return array The articles array
  60          */
  61         public function findAllAsArray(int $page, int $count): array {
  62                 //Set the request
  63                 $req = <<<SQL
  64 SELECT
  65         a.id,
  66         GREATEST(a.created, at.created, k.created, kt.created) AS created,
  67         GREATEST(a.updated, at.updated, k.updated, kt.updated) AS updated,
  68         GREATEST(a.created, at.created, k.created, kt.created, a.updated, at.updated, k.updated, kt.updated) as modified,
  69         at.body,
  70         at.description,
  71         at.slug,
  72         at.title,
  73         GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
  74         GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
  75         GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
  76 FROM RapsysBlogBundle:Article AS a
  77 JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
  78 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
  79 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
  80 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
  81 GROUP BY a.id
  82 ORDER BY updated, created DESC
  83 LIMIT :offset, :count
  84 SQL;
  85
  86                 //Replace bundle entity name by table name
  87                 $req = $this->replace($req);
  88
  89                 //Get result set mapping instance
  90                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  91                 $rsm = new ResultSetMapping();
  92
  93                 //Declare all fields
  94                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  95                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  96                 $rsm->addScalarResult('id', 'id', 'integer')
  97                         ->addScalarResult('body', 'body', 'string')
  98                         ->addScalarResult('description', 'description', 'string')
  99                         ->addScalarResult('slug', 'slug', 'string')
 100                         ->addScalarResult('title', 'title', 'string')
 101                         ->addScalarResult('k_ids', 'k_ids', 'string')
 102                         ->addScalarResult('k_slugs', 'k_slugs', 'string')
 103                         ->addScalarResult('k_titles', 'k_titles', 'string')
 104                         ->addScalarResult('created', 'created', 'datetime')
 105                         ->addScalarResult('updated', 'updated', 'datetime')
 106                         ->addScalarResult('modified', 'modified', 'datetime')
 107                         ->addIndexByScalar('id');
 108
 109                 //Get result
 110                 $result = $this->_em
 111                         ->createNativeQuery($req, $rsm)
 112                         ->setParameter('offset', $page * $count)
 113                         ->setParameter('count', $count)
 114                         ->getArrayResult();
 115
 116                 //Set return
 117                 $return = [];
 118
 119                 //Iterate on each city
 120                 foreach($result as $data) {
 121                         //Add to return
 122                         $return[$data['id']] = [
 123                                 'id' => $id = $data['id'],
 124                                 'body' => $data['body'],
 125                                 'description' => $data['description'],
 126                                 'slug' => $slug = $data['slug'],
 127                                 'title' => $data['title'],
 128                                 'created' => $data['created'],
 129                                 'updated' => $data['updated'],
 130                                 'modified' => $data['modified'],
 131                                 'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
 132                                 'keywords' => []
 133                         ];
 134
 135                         //Explode keyword ids
 136                         $data['k_ids'] = explode("\n", $data['k_ids']);
 137
 138                         //Explode keyword slugs
 139                         $data['k_slugs'] = explode("\n", $data['k_slugs']);
 140
 141                         //Explode keyword titles
 142                         $data['k_titles'] = explode("\n", $data['k_titles']);
 143
 144                         foreach($data['k_ids'] as $k => $id) {
 145                                 $return[$data['id']]['keywords'][$id] = [
 146                                         'id' => $id,
 147                                         'slug' => $slug = $data['k_slugs'][$k],
 148                                         'title' => $data['k_titles'][$k],
 149                                         'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug]),
 150                                 ];
 151                         }
 152                 }
 153
 154                 //Return return
 155                 return $return;
 156         }
 157
 158         /**
 159          * Find article by id as array
 160          *
 161          * @param integer $id The article id
 162          * @return array The article array
 163          */
 164         public function findByIdAsArray(int $id): array {
 165                 //Set the request
 166                 $req = <<<SQL
 167 SELECT
 168         b.id,
 169         GREATEST(b.created, kat.created) AS created,
 170         GREATEST(b.updated, kat.updated) AS updated,
 171         GREATEST(b.created, b.updated, kat.created, kat.updated) AS modified,
 172         b.body,
 173         b.description,
 174         b.slug,
 175         b.title,
 176         b.k_ids,
 177         b.k_slugs,
 178         b.k_titles,
 179         GROUP_CONCAT(kat.locale ORDER BY kat.locale SEPARATOR "\\n") AS kat_locales,
 180         GROUP_CONCAT(kat.slug ORDER BY kat.locale SEPARATOR "\\n") AS kat_slugs,
 181         GROUP_CONCAT(kat.title ORDER BY kat.locale SEPARATOR "\\n") AS kat_titles
 182 FROM (
 183         SELECT
 184                 a.id,
 185                 GREATEST(a.created, at.created, k.created, kt.created) AS created,
 186                 GREATEST(a.updated, at.updated, k.updated, kt.updated) AS updated,
 187                 at.body,
 188                 at.description,
 189                 at.slug,
 190                 at.title,
 191                 GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
 192                 GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
 193                 GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
 194         FROM RapsysBlogBundle:Article AS a
 195         JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
 196         LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
 197         LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
 198         LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
 199         WHERE a.id = :id
 200         GROUP BY a.id
 201         ORDER BY NULL
 202         LIMIT 0, :limit
 203 ) AS b
 204 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS kat ON (kat.article_id = b.id AND kat.locale <> :locale)
 205 GROUP BY b.id
 206 SQL;
 207
 208                 //Replace bundle entity name by table name
 209                 $req = $this->replace($req);
 210
 211                 //Get result set mapping instance
 212                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 213                 $rsm = new ResultSetMapping();
 214
 215                 //Declare all fields
 216                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 217                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 218                 $rsm->addScalarResult('id', 'id', 'integer')
 219                         ->addScalarResult('body', 'body', 'string')
 220                         ->addScalarResult('description', 'description', 'string')
 221                         ->addScalarResult('slug', 'slug', 'string')
 222                         ->addScalarResult('title', 'title', 'string')
 223                         ->addScalarResult('k_ids', 'k_ids', 'string')
 224                         ->addScalarResult('k_slugs', 'k_slugs', 'string')
 225                         ->addScalarResult('k_titles', 'k_titles', 'string')
 226                         ->addScalarResult('kat_locales', 'kat_locales', 'string')
 227                         ->addScalarResult('kat_slugs', 'kat_slugs', 'string')
 228                         ->addScalarResult('kat_titles', 'kat_titles', 'string')
 229                         ->addScalarResult('created', 'created', 'datetime')
 230                         ->addScalarResult('updated', 'updated', 'datetime')
 231                         ->addScalarResult('modified', 'modified', 'datetime');
 232
 233                 //Get data
 234                 $data = $this->_em
 235                         ->createNativeQuery($req, $rsm)
 236                         ->setParameter('id', $id)
 237                         ->getOneOrNullResult();
 238
 239                 //Set return
 240                 $return = [
 241                         'id' => $id = $data['id'],
 242                         'body' => $data['body'],
 243                         'description' => $data['description'],
 244                         'slug' => $slug = $data['slug'],
 245                         'title' => $data['title'],
 246                         'created' => $data['created'],
 247                         'updated' => $data['updated'],
 248                         'modified' => $data['modified'],
 249                         'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
 250                         'alternates' => [],
 251                         'keywords' => []
 252                 ];
 253
 254                 //Explode keyword ids
 255                 $data['k_ids'] = explode("\n", $data['k_ids']);
 256
 257                 //Explode keyword slugs
 258                 $data['k_slugs'] = explode("\n", $data['k_slugs']);
 259
 260                 //Explode keyword titles
 261                 $data['k_titles'] = explode("\n", $data['k_titles']);
 262
 263                 foreach($data['k_ids'] as $k => $kid) {
 264                         $return['keywords'][$kid] = [
 265                                 'id' => $kid,
 266                                 'slug' => $slug = $data['k_slugs'][$k],
 267                                 'title' => $data['k_titles'][$k],
 268                                 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slug]),
 269                         ];
 270                 }
 271
 272                 //Explode alternate locales
 273                 $data['kat_locales'] = explode("\n", $data['kat_locales']);
 274
 275                 //Explode alternate slugs
 276                 $data['kat_slugs'] = explode("\n", $data['kat_slugs']);
 277
 278                 //Explode alternate titles
 279                 $data['kat_titles'] = explode("\n", $data['kat_titles']);
 280
 281                 foreach($data['kat_locales'] as $k => $locale) {
 282                         $return['alternates'][$locale] = [
 283                                 'locale' => $locale,
 284                                 'slug' => $slug = $data['kat_slugs'][$k],
 285                                 'title' => $data['kat_titles'][$k],
 286                                 'link' => $this->router->generate('rapsys_blog_article_view', ['_locale' => $locale, 'id' => $id, 'slug' => $slug]),
 287                         ];
 288                 }
 289
 290                 //Return return
 291                 return $return;
 292         }
 293 }