Raphaël G. Git Repositories - blogbundle/blob - Repository/UserRepository.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 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
  16 use Symfony\Component\Security\Core\User\PasswordAuthenticatedUserInterface;
  17 use Symfony\Component\Security\Core\User\PasswordUpgraderInterface;
  18
  19 /**
  20  * UserRepository
  21  */
  22 class UserRepository extends EntityRepository implements PasswordUpgraderInterface {
  23         /**
  24          * Find user 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(u.id) AS count
  32 FROM RapsysBlogBundle:User AS u
  33 SQL;
  34
  35                 //Get result set mapping instance
  36                 $req = $this->replace($req);
  37
  38                 //Get result set mapping instance
  39                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  40                 $rsm = new ResultSetMapping();
  41
  42                 //Declare all fields
  43                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  44                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  45                 $rsm->addScalarResult('count', 'count', 'integer');
  46
  47                 //Get result
  48                 return $this->_em
  49                         ->createNativeQuery($req, $rsm)
  50                         ->getSingleScalarResult();
  51         }
  52
  53         /**
  54          * Find all users as array
  55          *
  56          * @param integer $page The page
  57          * @param integer $count The count
  58          * @return array The users sorted by id
  59          */
  60         public function findAllAsArray(int $page, int $count): array {
  61                 //Set the request
  62                 $req = <<<SQL
  63 SELECT
  64         t.id,
  65         t.mail,
  66         t.forename,
  67         t.surname,
  68         t.pseudonym,
  69         t.slug,
  70         t.created,
  71         t.updated,
  72         GREATEST(t.created, t.updated) AS modified,
  73         t.civility,
  74         t.g_ids,
  75         t.g_titles,
  76         GROUP_CONCAT(t.a_id ORDER BY t.a_id SEPARATOR "\\n") AS a_ids,
  77         GROUP_CONCAT(t.at_description ORDER BY t.a_id SEPARATOR "\\n") AS at_descriptions,
  78         GROUP_CONCAT(t.at_slug ORDER BY t.a_id SEPARATOR "\\n") AS at_slugs,
  79         GROUP_CONCAT(t.at_title ORDER BY t.a_id SEPARATOR "\\n") AS at_titles,
  80         GROUP_CONCAT(t.ak_ids ORDER BY t.a_id SEPARATOR "\\n") AS ak_ids,
  81         GROUP_CONCAT(t.kt_slugs ORDER BY t.a_id SEPARATOR "\\n") AS kt_slugs,
  82         GROUP_CONCAT(t.kt_titles ORDER BY t.a_id SEPARATOR "\\n") AS kt_titles
  83 FROM (
  84         SELECT
  85                 c.id,
  86                 c.mail,
  87                 c.forename,
  88                 c.surname,
  89                 c.pseudonym,
  90                 c.slug,
  91                 GREATEST(c.created, COALESCE(a.created, 0), COALESCE(at.created, 0), COALESCE(k.created, 0), COALESCE(kt.created, 0)) AS created,
  92                 GREATEST(c.updated, COALESCE(a.updated, 0), COALESCE(at.updated, 0), COALESCE(k.updated, 0), COALESCE(kt.updated, 0)) AS updated,
  93                 c.civility,
  94                 c.g_ids,
  95                 c.g_titles,
  96                 a.id AS a_id,
  97                 at.description AS at_description,
  98                 at.slug AS at_slug,
  99                 at.title AS at_title,
 100                 GROUP_CONCAT(ak.keyword_id ORDER BY ak.keyword_id SEPARATOR "\\r") AS ak_ids,
 101                 GROUP_CONCAT(kt.slug ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_slugs,
 102                 GROUP_CONCAT(kt.title ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_titles
 103         FROM (
 104                 SELECT
 105                         u.id,
 106                         u.mail,
 107                         u.forename,
 108                         u.surname,
 109                         u.pseudonym,
 110                         u.slug,
 111                         GREATEST(u.created, c.created, g.created) AS created,
 112                         GREATEST(u.updated, c.updated, g.updated) AS updated,
 113                         c.title AS civility,
 114                         GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS g_ids,
 115                         GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS g_titles
 116                 FROM RapsysBlogBundle:User AS u
 117                 JOIN RapsysBlogBundle:UserGroup AS gu ON (gu.user_id = u.id)
 118                 JOIN RapsysBlogBundle:Group AS g ON (g.id = gu.group_id)
 119                 JOIN RapsysBlogBundle:Civility AS c ON (c.id = u.civility_id)
 120                 GROUP BY u.id
 121                 ORDER BY NULL
 122                 LIMIT 0, :limit
 123         ) AS c
 124         LEFT JOIN RapsysBlogBundle:Article AS a ON (a.user_id = c.id)
 125         LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
 126         LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
 127         LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
 128         LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak.keyword_id AND at.locale = :locale)
 129         GROUP BY a.id
 130         ORDER BY NULL
 131         LIMIT 0, :limit
 132 ) AS t
 133 GROUP BY t.id
 134 ORDER BY t.id ASC
 135 LIMIT :offset, :count
 136 SQL;
 137
 138                 //Replace bundle entity name by table name
 139                 $req = $this->replace($req);
 140
 141                 //Get result set mapping instance
 142                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/UserBundle/Repository/ArticleRepository.php
 143                 $rsm = new ResultSetMapping();
 144
 145                 //Declare all fields
 146                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 147                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 148                 $rsm->addScalarResult('id', 'id', 'integer')
 149                         ->addScalarResult('mail', 'mail', 'string')
 150                         ->addScalarResult('forename', 'forename', 'string')
 151                         ->addScalarResult('surname', 'surname', 'string')
 152                         ->addScalarResult('pseudonym', 'pseudonym', 'string')
 153                         ->addScalarResult('slug', 'slug', 'string')
 154                         ->addScalarResult('created', 'created', 'datetime')
 155                         ->addScalarResult('updated', 'updated', 'datetime')
 156                         ->addScalarResult('modified', 'modified', 'datetime')
 157                         ->addScalarResult('civility', 'civility', 'string')
 158                         //XXX: is a string because of \n separator
 159                         ->addScalarResult('g_ids', 'g_ids', 'string')
 160                         //XXX: is a string because of \n separator
 161                         ->addScalarResult('g_titles', 'g_titles', 'string')
 162                         //XXX: is a string because of \n separator
 163                         ->addScalarResult('a_ids', 'a_ids', 'string')
 164                         //XXX: is a string because of \n separator
 165                         ->addScalarResult('at_descriptions', 'at_descriptions', 'string')
 166                         //XXX: is a string because of \n separator
 167                         ->addScalarResult('at_slugs', 'at_slugs', 'string')
 168                         //XXX: is a string because of \n separator
 169                         ->addScalarResult('at_titles', 'at_titles', 'string')
 170                         //XXX: is a string because of \n separator
 171                         ->addScalarResult('ak_ids', 'ak_ids', 'string')
 172                         //XXX: is a string because of \n separator
 173                         ->addScalarResult('kt_slugs', 'kt_slugs', 'string')
 174                         //XXX: is a string because of \n separator
 175                         ->addScalarResult('kt_titles', 'kt_titles', 'string');
 176
 177                 //Fetch result
 178                 $res = $this->_em
 179                         ->createNativeQuery($req, $rsm)
 180                         ->setParameter('offset', $page * $count)
 181                         ->setParameter('count', $count)
 182                         ->getResult();
 183
 184                 //Init return
 185                 $ret = [];
 186
 187                 //Process result
 188                 foreach($res as $data) {
 189                         //Set data
 190                         $ret[$data['id']] = [
 191                                 'mail' => $data['mail'],
 192                                 'forename' => $data['forename'],
 193                                 'surname' => $data['surname'],
 194                                 'pseudonym' => $data['pseudonym'],
 195                                 'created' => $data['created'],
 196                                 'updated' => $data['updated'],
 197                                 'modified' => $data['modified'],
 198                                 'civility' => $data['civility'],
 199                                 #'slug' => $data['slug'],
 200                                 'link' => $this->router->generate('rapsys_blog_user_view', ['id' => $data['id'], 'slug' => $data['slug']]),
 201                                 'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)]),
 202                                 'articles' => [],
 203                                 'groups' => []
 204                         ];
 205
 206                         //With groups
 207                         if (!empty($data['g_ids'])) {
 208                                 //Set titles
 209                                 $titles = explode("\n", $data['g_titles']);
 210
 211                                 //Iterate on each group
 212                                 foreach(explode("\n", $data['g_ids']) as $k => $id) {
 213                                         //Add group
 214                                         $ret[$data['id']]['groups'][$id] = [
 215                                                 'title' => /*$group = */$this->translator->trans($titles[$k]),
 216                                                 #'slug' => $this->slugger->slug($group)
 217                                                 #'link' => $this->router->generate('rapsys_user_group_view', ['id' => $id, 'slug' => $this->slugger->short($group)])
 218                                         ];
 219                                 }
 220                         }
 221
 222                         //With articles
 223                         if (!empty($data['a_ids'])) {
 224                                 //Set descriptions
 225                                 $descriptions = explode("\n", $data['at_descriptions']);
 226
 227                                 //Set slugs
 228                                 $slugs = explode("\n", $data['at_slugs']);
 229
 230                                 //Set titles
 231                                 $titles = explode("\n", $data['at_titles']);
 232
 233                                 //Set keyword ids
 234                                 $keywords = [
 235                                         'ids' => explode("\n", $data['ak_ids']),
 236                                         'slugs' => explode("\n", $data['kt_slugs']),
 237                                         'titles' => explode("\n", $data['kt_titles'])
 238                                 ];
 239
 240                                 //Iterate on each dance
 241                                 foreach(explode("\n", $data['a_ids']) as $k => $id) {
 242                                         //Init article when missing
 243                                         if (!isset($ret[$data['id']]['articles'][$id])) {
 244                                                 //Add article
 245                                                 $ret[$data['id']]['articles'][$id] = [
 246                                                         'description' => $descriptions[$k],
 247                                                         #'slug' => $slugs[$k],
 248                                                         'title' => $titles[$k],
 249                                                         'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slugs[$k]]),
 250                                                         //TODO: replace with keywords !!!
 251                                                         'keywords' => []
 252                                                 ];
 253
 254                                                 //With article keywords
 255                                                 if (!empty($keywords['ids'][$k])) {
 256                                                         //Set slugs
 257                                                         $slugs = explode("\r", $keywords['slugs'][$k]);
 258
 259                                                         //Set titles
 260                                                         $titles = explode("\r", $keywords['titles'][$k]);
 261
 262                                                         //Iterate on each keyword
 263                                                         foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
 264                                                                 //Add keyword
 265                                                                 $ret[$data['id']]['articles'][$id]['keywords'][$kid] = [
 266                                                                         #'slug' => $slugs[$k],
 267                                                                         'title' => $titles[$k],
 268                                                                         'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slugs[$k]]),
 269                                                                 ];
 270                                                         }
 271                                                 }
 272                                         }
 273                                 }
 274                         }
 275                 }
 276
 277                 //Send result
 278                 return $ret;
 279         }
 280
 281         /**
 282          * Find user by id as array
 283          *
 284          * @param integer $id The user id
 285          * @return array The article array
 286          */
 287         public function findByIdAsArray(int $id): array {
 288                 //Set the request
 289                 $req = <<<SQL
 290 SELECT
 291         t.id,
 292         t.mail,
 293         t.forename,
 294         t.surname,
 295         t.pseudonym,
 296         t.slug,
 297         t.created,
 298         t.updated,
 299         GREATEST(t.created, t.updated) AS modified,
 300         t.civility,
 301         t.g_ids,
 302         t.g_titles,
 303         GROUP_CONCAT(t.a_id ORDER BY t.a_id SEPARATOR "\\n") AS a_ids,
 304         GROUP_CONCAT(t.at_description ORDER BY t.a_id SEPARATOR "\\n") AS at_descriptions,
 305         GROUP_CONCAT(t.at_slug ORDER BY t.a_id SEPARATOR "\\n") AS at_slugs,
 306         GROUP_CONCAT(t.at_title ORDER BY t.a_id SEPARATOR "\\n") AS at_titles,
 307         GROUP_CONCAT(t.ak_ids ORDER BY t.a_id SEPARATOR "\\n") AS ak_ids,
 308         GROUP_CONCAT(t.kt_slugs ORDER BY t.a_id SEPARATOR "\\n") AS kt_slugs,
 309         GROUP_CONCAT(t.kt_titles ORDER BY t.a_id SEPARATOR "\\n") AS kt_titles
 310 FROM (
 311         SELECT
 312                 c.id,
 313                 c.mail,
 314                 c.forename,
 315                 c.surname,
 316                 c.pseudonym,
 317                 c.slug,
 318                 GREATEST(c.created, COALESCE(a.created, 0), COALESCE(at.created, 0), COALESCE(k.created, 0), COALESCE(kt.created, 0)) AS created,
 319                 GREATEST(c.updated, COALESCE(a.updated, 0), COALESCE(at.updated, 0), COALESCE(k.updated, 0), COALESCE(kt.updated, 0)) AS updated,
 320                 c.civility,
 321                 c.g_ids,
 322                 c.g_titles,
 323                 a.id AS a_id,
 324                 at.description AS at_description,
 325                 at.slug AS at_slug,
 326                 at.title AS at_title,
 327                 GROUP_CONCAT(ak.keyword_id ORDER BY ak.keyword_id SEPARATOR "\\r") AS ak_ids,
 328                 GROUP_CONCAT(kt.slug ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_slugs,
 329                 GROUP_CONCAT(kt.title ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_titles
 330         FROM (
 331                 SELECT
 332                         u.id,
 333                         u.mail,
 334                         u.forename,
 335                         u.surname,
 336                         u.pseudonym,
 337                         u.slug,
 338                         GREATEST(u.created, c.created, g.created) AS created,
 339                         GREATEST(u.updated, c.updated, g.updated) AS updated,
 340                         c.title AS civility,
 341                         GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS g_ids,
 342                         GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS g_titles
 343                 FROM RapsysBlogBundle:User AS u
 344                 JOIN RapsysBlogBundle:UserGroup AS gu ON (gu.user_id = u.id)
 345                 JOIN RapsysBlogBundle:Group AS g ON (g.id = gu.group_id)
 346                 JOIN RapsysBlogBundle:Civility AS c ON (c.id = u.civility_id)
 347                 WHERE u.id = :id
 348                 GROUP BY u.id
 349                 ORDER BY NULL
 350                 LIMIT 0, :limit
 351         ) AS c
 352         LEFT JOIN RapsysBlogBundle:Article AS a ON (a.user_id = c.id)
 353         LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
 354         LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
 355         LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
 356         LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak.keyword_id AND at.locale = :locale)
 357         GROUP BY a.id
 358         ORDER BY NULL
 359         LIMIT 0, :limit
 360 ) AS t
 361 GROUP BY t.id
 362 SQL;
 363
 364                 //Replace bundle entity name by table name
 365                 $req = $this->replace($req);
 366
 367                 //Get result set mapping instance
 368                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 369                 $rsm = new ResultSetMapping();
 370
 371                 //Declare all fields
 372                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 373                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 374                 $rsm->addScalarResult('id', 'id', 'integer')
 375                         ->addScalarResult('mail', 'mail', 'string')
 376                         ->addScalarResult('forename', 'forename', 'string')
 377                         ->addScalarResult('surname', 'surname', 'string')
 378                         ->addScalarResult('pseudonym', 'pseudonym', 'string')
 379                         ->addScalarResult('slug', 'slug', 'string')
 380                         ->addScalarResult('created', 'created', 'datetime')
 381                         ->addScalarResult('updated', 'updated', 'datetime')
 382                         ->addScalarResult('modified', 'modified', 'datetime')
 383                         ->addScalarResult('civility', 'civility', 'string')
 384                         //XXX: is a string because of \n separator
 385                         ->addScalarResult('g_ids', 'g_ids', 'string')
 386                         //XXX: is a string because of \n separator
 387                         ->addScalarResult('g_titles', 'g_titles', 'string')
 388                         //XXX: is a string because of \n separator
 389                         ->addScalarResult('a_ids', 'a_ids', 'string')
 390                         //XXX: is a string because of \n separator
 391                         ->addScalarResult('at_descriptions', 'at_descriptions', 'string')
 392                         //XXX: is a string because of \n separator
 393                         ->addScalarResult('at_slugs', 'at_slugs', 'string')
 394                         //XXX: is a string because of \n separator
 395                         ->addScalarResult('at_titles', 'at_titles', 'string')
 396                         //XXX: is a string because of \n separator
 397                         ->addScalarResult('ak_ids', 'ak_ids', 'string')
 398                         //XXX: is a string because of \n separator
 399                         ->addScalarResult('kt_slugs', 'kt_slugs', 'string')
 400                         //XXX: is a string because of \n separator
 401                         ->addScalarResult('kt_titles', 'kt_titles', 'string');
 402
 403                 //Get data
 404                 $data = $this->_em
 405                         ->createNativeQuery($req, $rsm)
 406                         ->setParameter('id', $id)
 407                         ->getOneOrNullResult();
 408
 409                 //Set return
 410                 $return = [
 411                         'id' => $data['id'],
 412                         'mail' => $data['mail'],
 413                         'forename' => $data['forename'],
 414                         'surname' => $data['surname'],
 415                         'pseudonym' => $data['pseudonym'],
 416                         'created' => $data['created'],
 417                         'updated' => $data['updated'],
 418                         'modified' => $data['modified'],
 419                         'civility' => $data['civility'],
 420                         'slug' => $data['slug'],
 421                         'link' => $this->router->generate('rapsys_blog_user_view', ['id' => $data['id'], 'slug' => $data['slug']]),
 422                         'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)]),
 423                         'articles' => [],
 424                         'groups' => []
 425                 ];
 426
 427                 //With groups
 428                 if (!empty($data['g_ids'])) {
 429                         //Set titles
 430                         $titles = explode("\n", $data['g_titles']);
 431
 432                         //Iterate on each group
 433                         foreach(explode("\n", $data['g_ids']) as $k => $id) {
 434                                 //Add group
 435                                 $return['groups'][$id] = [
 436                                         'title' => /*$group = */$this->translator->trans($titles[$k]),
 437                                         #'slug' => $this->slugger->slug($group)
 438                                         #'link' => $this->router->generate('rapsys_user_group_view', ['id' => $id, 'slug' => $this->slugger->short($group)])
 439                                 ];
 440                         }
 441                 }
 442
 443                 //With articles
 444                 if (!empty($data['a_ids'])) {
 445                         //Set descriptions
 446                         $descriptions = explode("\n", $data['at_descriptions']);
 447
 448                         //Set slugs
 449                         $slugs = explode("\n", $data['at_slugs']);
 450
 451                         //Set titles
 452                         $titles = explode("\n", $data['at_titles']);
 453
 454                         //Set keyword ids
 455                         $keywords = [
 456                                 'ids' => explode("\n", $data['ak_ids']),
 457                                 'slugs' => explode("\n", $data['kt_slugs']),
 458                                 'titles' => explode("\n", $data['kt_titles'])
 459                         ];
 460
 461                         //Iterate on each dance
 462                         foreach(explode("\n", $data['a_ids']) as $k => $id) {
 463                                 //Init article when missing
 464                                 if (!isset($ret[$data['id']]['articles'][$id])) {
 465                                         //Add article
 466                                         $return['articles'][$id] = [
 467                                                 'description' => $descriptions[$k],
 468                                                 #'slug' => $slugs[$k],
 469                                                 'title' => $titles[$k],
 470                                                 'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slugs[$k]]),
 471                                                 //TODO: replace with keywords !!!
 472                                                 'keywords' => []
 473                                         ];
 474
 475                                         //With article keywords
 476                                         if (!empty($keywords['ids'][$k])) {
 477                                                 //Set slugs
 478                                                 $slugs = explode("\r", $keywords['slugs'][$k]);
 479
 480                                                 //Set titles
 481                                                 $titles = explode("\r", $keywords['titles'][$k]);
 482
 483                                                 //Iterate on each keyword
 484                                                 foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
 485                                                         //Add keyword
 486                                                         $return['articles'][$id]['keywords'][$kid] = [
 487                                                                 #'slug' => $slugs[$k],
 488                                                                 'title' => $titles[$k],
 489                                                                 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slugs[$k]]),
 490                                                         ];
 491                                                 }
 492                                         }
 493                                 }
 494                         }
 495                 }
 496
 497                 //Return return
 498                 return $return;
 499         }
 500
 501         /**
 502          * {@inheritdoc}
 503          */
 504         public function upgradePassword(PasswordAuthenticatedUserInterface $user, string $hash): void {
 505                 //Set new hashed password
 506                 $user->setPassword($hash);
 507
 508                 //Flush data to database
 509                 $this->getEntityManager()->flush();
 510         }
 511 }