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