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         b.created,
 170         b.updated,
 171         b.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                 GREATEST(a.created, at.created, k.created, kt.created, a.updated, at.updated, k.updated, kt.updated) AS modified,
 188                 at.body,
 189                 at.description,
 190                 at.slug,
 191                 at.title,
 192                 GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
 193                 GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
 194                 GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
 195         FROM RapsysBlogBundle:Article AS a
 196         JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
 197         LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
 198         LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
 199         LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
 200         WHERE a.id = :id
 201         GROUP BY a.id
 202         ORDER BY NULL
 203         LIMIT 0, :limit
 204 ) AS b
 205 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS kat ON (kat.article_id = b.id AND kat.locale <> :locale)
 206 GROUP BY b.id
 207 SQL;
 208
 209                 //Replace bundle entity name by table name
 210                 $req = $this->replace($req);
 211
 212                 //Get result set mapping instance
 213                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 214                 $rsm = new ResultSetMapping();
 215
 216                 //Declare all fields
 217                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 218                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 219                 $rsm->addScalarResult('id', 'id', 'integer')
 220                         ->addScalarResult('body', 'body', 'string')
 221                         ->addScalarResult('description', 'description', 'string')
 222                         ->addScalarResult('slug', 'slug', 'string')
 223                         ->addScalarResult('title', 'title', 'string')
 224                         ->addScalarResult('k_ids', 'k_ids', 'string')
 225                         ->addScalarResult('k_slugs', 'k_slugs', 'string')
 226                         ->addScalarResult('k_titles', 'k_titles', 'string')
 227                         ->addScalarResult('kat_locales', 'kat_locales', 'string')
 228                         ->addScalarResult('kat_slugs', 'kat_slugs', 'string')
 229                         ->addScalarResult('kat_titles', 'kat_titles', 'string')
 230                         ->addScalarResult('created', 'created', 'datetime')
 231                         ->addScalarResult('updated', 'updated', 'datetime')
 232                         ->addScalarResult('modified', 'modified', 'datetime');
 233
 234                 //Get data
 235                 $data = $this->_em
 236                         ->createNativeQuery($req, $rsm)
 237                         ->setParameter('id', $id)
 238                         ->getOneOrNullResult();
 239
 240                 //Set return
 241                 $return = [
 242                         'id' => $id = $data['id'],
 243                         'body' => $data['body'],
 244                         'description' => $data['description'],
 245                         'slug' => $slug = $data['slug'],
 246                         'title' => $data['title'],
 247                         'created' => $data['created'],
 248                         'updated' => $data['updated'],
 249                         'modified' => $data['modified'],
 250                         'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
 251                         'alternates' => [],
 252                         'keywords' => []
 253                 ];
 254
 255                 //Explode keyword ids
 256                 $data['k_ids'] = explode("\n", $data['k_ids']);
 257
 258                 //Explode keyword slugs
 259                 $data['k_slugs'] = explode("\n", $data['k_slugs']);
 260
 261                 //Explode keyword titles
 262                 $data['k_titles'] = explode("\n", $data['k_titles']);
 263
 264                 foreach($data['k_ids'] as $k => $kid) {
 265                         $return['keywords'][$kid] = [
 266                                 'id' => $kid,
 267                                 'slug' => $slug = $data['k_slugs'][$k],
 268                                 'title' => $data['k_titles'][$k],
 269                                 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slug]),
 270                         ];
 271                 }
 272
 273                 //Explode alternate locales
 274                 $data['kat_locales'] = explode("\n", $data['kat_locales']);
 275
 276                 //Explode alternate slugs
 277                 $data['kat_slugs'] = explode("\n", $data['kat_slugs']);
 278
 279                 //Explode alternate titles
 280                 $data['kat_titles'] = explode("\n", $data['kat_titles']);
 281
 282                 foreach($data['kat_locales'] as $k => $locale) {
 283                         $return['alternates'][$locale] = [
 284                                 'locale' => $locale,
 285                                 'slug' => $slug = $data['kat_slugs'][$k],
 286                                 'title' => $data['kat_titles'][$k],
 287                                 'link' => $this->router->generate('rapsys_blog_article_view', ['_locale' => $locale, 'id' => $id, 'slug' => $slug]),
 288                         ];
 289                 }
 290
 291                 //Return return
 292                 return $return;
 293         }
 294 }