Add findByIdAsArray function

[blogbundle] / Repository / KeywordRepository.php

diff --git a/Repository/KeywordRepository.php b/Repository/KeywordRepository.php
index 08e0d220b787b34d2077f6c8b787383212ace173..f531711d1b189ac22243988c7c55efb5eaf50ab4 100644 (file)
--- a/Repository/KeywordRepository.php
+++ b/Repository/KeywordRepository.php
@@ -1,70 +1,324 @@
-<?php
+<?php declare(strict_types=1);
+
+/*
+ * This file is part of the Rapsys BlogBundle package.
+ *
+ * (c) Raphaël Gertz <symfony@rapsys.eu>
+ *
+ * For the full copyright and license information, please view the LICENSE
+ * file that was distributed with this source code.
+ */

 
 namespace Rapsys\BlogBundle\Repository;
 
 
 namespace Rapsys\BlogBundle\Repository;
 

+use Doctrine\ORM\Query\ResultSetMapping;
+

 /**
  * KeywordRepository
  *
  * This class was generated by the Doctrine ORM. Add your own custom
  * repository methods below.
  */
 /**
  * KeywordRepository
  *
  * This class was generated by the Doctrine ORM. Add your own custom
  * repository methods below.
  */

-class KeywordRepository extends \Doctrine\ORM\EntityRepository {
+class KeywordRepository extends EntityRepository {

        /**
        /**

-        * Find keyword
+        * Find keyword count as int

         *
         *

-        * @param string $_locale
-        * @param string $_keyword
+        * @return integer The keywords count

         */
         */

-       public function findKeyword($_locale, $_keyword) {
-               //Fetch keyword
-               $ret = $this->getEntityManager()
-                       ->createQuery('SELECT k.id, k.created, k.updated, kt.slug, kt.title, kt.description, JSON(at.slug, at.title) AS articles, \'\' AS slugs FROM RapsysBlogBundle:Keyword k JOIN RapsysBlogBundle:KeywordTranslation kt WITH (kt.keyword_id = k.id AND kt.slug = :_keyword) JOIN RapsysBlogBundle:Language AS l WITH (l.id = kt.language_id AND l.iso6391 = :_locale) LEFT JOIN RapsysBlogBundle:ArticleKeyword ak WITH (ak.keyword_id = k.id) LEFT JOIN RapsysBlogBundle:ArticleTranslation at WITH (at.article_id = ak.article_id AND at.language_id = kt.language_id) GROUP BY k.id')
-                       ->setParameter('_locale', $_locale)
-                       ->setParameter('_keyword', $_keyword)
-                       ->getSingleResult();
-
-               //Decode json
-               if (!empty($ret['articles'])) {
-                       $ret['articles'] = json_decode($ret['articles'], true);
-               }
+       public function findCountAsInt(): int {
+               //Set the request
+               $req = <<<SQL
+SELECT COUNT(kt.keyword_id) AS count
+FROM RapsysBlogBundle:KeywordTranslation AS kt
+WHERE kt.locale = :locale
+SQL;

 
 

-               //Fetch keyword's slugs in other locale
-               $slugs = $this->getEntityManager()
-                       ->createQuery('SELECT JSON(bl.iso6391, bkt.slug) AS slugs FROM RapsysBlogBundle:Language bl LEFT JOIN RapsysBlogBundle:KeywordTranslation bkt WITH (bkt.keyword_id = :_keyword AND bkt.language_id = bl.id) WHERE (bl.iso6391 <> :_locale) GROUP BY bkt.keyword_id')
-                       ->setParameter('_locale', $_locale)
-                       ->setParameter('_keyword', $ret['id'])
-                       ->getSingleResult();
+               //Get result set mapping instance
+               $req = $this->replace($req);

 
 

-               //Decode json
-               if (!empty($slugs['slugs'])) {
-                       $ret['slugs'] = json_decode($slugs['slugs'], true);
+               //Get result set mapping instance
+               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+               $rsm = new ResultSetMapping();
+
+               //Declare all fields
+               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+               //addScalarResult($sqlColName, $resColName, $type = 'string');
+               $rsm->addScalarResult('count', 'count', 'integer');
+
+               //Get result
+               return $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->getSingleScalarResult();
+       }
+
+       /**
+        * Find keywords as array
+        *
+        * @param integer $page The page
+        * @param integer $count The count
+        * @return array The keywords array
+        */
+       public function findAllAsArray(int $page, int $count): array {
+               //Set the request
+               $req = <<<SQL
+SELECT
+       k.id,
+       GREATEST(k.created, kt.created) AS created,
+       GREATEST(k.updated, kt.updated) AS updated,
+       GREATEST(k.created, kt.created, k.updated, kt.updated) AS modified,
+       kt.description,
+       kt.slug,
+       kt.title
+FROM RapsysBlogBundle:Keyword AS k
+JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
+GROUP BY k.id
+ORDER BY updated, created DESC
+LIMIT :offset, :count
+SQL;
+
+               //Replace bundle entity name by table name
+               $req = $this->replace($req);
+
+               //Get result set mapping instance
+               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+               $rsm = new ResultSetMapping();
+
+               //Declare all fields
+               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+               //addScalarResult($sqlColName, $resColName, $type = 'string');
+               $rsm->addScalarResult('id', 'id', 'integer')
+                       ->addScalarResult('description', 'description', 'string')
+                       ->addScalarResult('slug', 'slug', 'string')
+                       ->addScalarResult('title', 'title', 'string')
+                       ->addScalarResult('created', 'created', 'datetime')
+                       ->addScalarResult('updated', 'updated', 'datetime')
+                       ->addScalarResult('modified', 'modified', 'datetime')
+                       ->addIndexByScalar('id');
+
+               //Get result
+               $result = $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->setParameter('offset', $page * $count)
+                       ->setParameter('count', $count)
+                       ->getArrayResult();
+
+               //Set return
+               $return = [];
+
+               //Iterate on each city
+               foreach($result as $data) {
+                       //Add to return
+                       $return[$data['id']] = [
+                               'id' => $id = $data['id'],
+                               'description' => $data['description'],
+                               'slug' => $slug = $data['slug'],
+                               'title' => $data['title'],
+                               'created' => $data['created'],
+                               'updated' => $data['updated'],
+                               'modified' => $data['modified'],
+                               'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug])
+                       ];

                }
 
                }
 

-               //Send result
-               return $ret;
+               //Return return
+               return $return;

        }
 
        /**
        }
 
        /**

-        * Find keywords
+        * Find keyword by id as array

         *
         *

-        * @param string $_locale
+        * @param integer $id The keyword id
+        * @return array The keyword array

         */
         */

-       public function findKeywords($_locale) {
-               //Fetch keywords
-               $ret = $this->getEntityManager()
-                       #, JSON(kt.keyword_id, kt.title) AS keywords
-                        ->createQuery('SELECT k.id, k.created, k.updated, kt.slug, kt.title, JSON(at.slug, at.title) AS articles FROM RapsysBlogBundle:Keyword k JOIN RapsysBlogBundle:KeywordTranslation kt WITH (kt.keyword_id = k.id) JOIN RapsysBlogBundle:Language AS l WITH (l.id = kt.language_id AND l.iso6391 = :_locale) LEFT JOIN RapsysBlogBundle:ArticleKeyword ak WITH (ak.keyword_id = k.id) LEFT JOIN RapsysBlogBundle:ArticleTranslation at WITH (at.article_id = ak.article_id AND at.language_id = kt.language_id) GROUP BY k.id')
-                        ->setParameter('_locale', $_locale)
-                       ->execute();
-               //Decode json
-               if (!empty($ret)) {
-                       foreach ($ret as $id => $keyword) {
-                               if (!empty($keyword['articles'])) {
-                                       $ret[$id]['articles'] = json_decode($keyword['articles'], true);
-                               }
-                       }
+       public function findByIdAsArray(int $id): array {
+               //Set the request
+               $req = <<<SQL
+SELECT
+       c.id,
+       c.created,
+       c.updated,
+       c.modified,
+       c.description,
+       c.slug,
+       c.title,
+       c.a_ids,
+       c.a_descriptions,
+       c.a_slugs,
+       c.a_titles,
+       c.ak_aids,
+       c.ak_kids,
+       c.ak_slugs,
+       c.ak_titles,
+       GROUP_CONCAT(ckt.locale ORDER BY ckt.locale SEPARATOR "\\n") AS ckt_locales,
+       GROUP_CONCAT(ckt.slug ORDER BY ckt.locale SEPARATOR "\\n") AS ckt_slugs,
+       GROUP_CONCAT(ckt.title ORDER BY ckt.locale SEPARATOR "\\n") AS ckt_titles
+FROM (
+       SELECT
+               b.id,
+               b.created,
+               b.updated,
+               GREATEST(b.modified, k.created, k.updated) AS modified,
+               b.description,
+               b.slug,
+               b.title,
+               b.a_ids,
+               b.a_descriptions,
+               b.a_slugs,
+               b.a_titles,
+               GROUP_CONCAT(ak2.article_id ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_aids,
+               GROUP_CONCAT(ak2.keyword_id ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_kids,
+               GROUP_CONCAT(kt.slug ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_slugs,
+               GROUP_CONCAT(kt.title ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_titles
+       FROM (
+               SELECT
+                       k.id,
+                       GREATEST(k.created, kt.created) AS created,
+                       GREATEST(k.updated, kt.updated) AS updated,
+                       GREATEST(k.created, kt.created, a.created, at.created, k.updated, kt.updated, a.updated, at.updated) AS modified,
+                       kt.description,
+                       kt.slug,
+                       kt.title,
+                       GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR "\\n") AS a_ids,
+                       GROUP_CONCAT(at.description ORDER BY a.id SEPARATOR "\\n") AS a_descriptions,
+                       GROUP_CONCAT(at.slug ORDER BY a.id SEPARATOR "\\n") AS a_slugs,
+                       GROUP_CONCAT(at.title ORDER BY a.id SEPARATOR "\\n") AS a_titles
+               FROM RapsysBlogBundle:Keyword AS k
+               JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
+               LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.keyword_id = k.id)
+               LEFT JOIN RapsysBlogBundle:Article AS a ON (a.id = ak.article_id)
+               LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
+               WHERE k.id = :id
+               GROUP BY k.id
+               ORDER BY NULL
+               LIMIT 0, :limit
+       ) AS b
+       LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.keyword_id = b.id)
+       LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak2 ON (ak2.article_id = ak.article_id)
+       LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak2.keyword_id)
+       LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak2.keyword_id AND kt.locale = :locale)
+       GROUP BY b.id
+       ORDER BY NULL
+       LIMIT 0, :limit
+) AS c
+LEFT JOIN RapsysBlogBundle:KeywordTranslation AS ckt ON (ckt.keyword_id = c.id AND ckt.locale <> :locale)
+GROUP BY c.id
+SQL;
+
+               //Replace bundle entity name by table name
+               $req = $this->replace($req);
+
+               //Get result set mapping instance
+               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+               $rsm = new ResultSetMapping();
+
+               //Declare all fields
+               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+               //addScalarResult($sqlColName, $resColName, $type = 'string');
+               $rsm->addScalarResult('id', 'id', 'integer')
+                       ->addScalarResult('body', 'body', 'string')
+                       ->addScalarResult('description', 'description', 'string')
+                       ->addScalarResult('slug', 'slug', 'string')
+                       ->addScalarResult('title', 'title', 'string')
+                       ->addScalarResult('a_ids', 'a_ids', 'string')
+                       ->addScalarResult('a_descriptions', 'a_descriptions', 'string')
+                       ->addScalarResult('a_slugs', 'a_slugs', 'string')
+                       ->addScalarResult('a_titles', 'a_titles', 'string')
+                       ->addScalarResult('ak_aids', 'ak_aids', 'string')
+                       ->addScalarResult('ak_kids', 'ak_kids', 'string')
+                       ->addScalarResult('ak_slugs', 'ak_slugs', 'string')
+                       ->addScalarResult('ak_titles', 'ak_titles', 'string')
+                       ->addScalarResult('ckt_locales', 'ckt_locales', 'string')
+                       ->addScalarResult('ckt_slugs', 'ckt_slugs', 'string')
+                       ->addScalarResult('ckt_titles', 'ckt_titles', 'string')
+                       ->addScalarResult('created', 'created', 'datetime')
+                       ->addScalarResult('updated', 'updated', 'datetime')
+                       ->addScalarResult('modified', 'modified', 'datetime');
+
+               //Get data
+               $data = $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->setParameter('id', $id)
+                       ->getOneOrNullResult();
+
+               //Set return
+               $return = [
+                       'id' => $id = $data['id'],
+                       'description' => $data['description'],
+                       'slug' => $slug = $data['slug'],
+                       'title' => $data['title'],
+                       'created' => $data['created'],
+                       'updated' => $data['updated'],
+                       'modified' => $data['modified'],
+                       'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug]),
+                       'alternates' => [],
+                       'articles' => []
+               ];
+
+               //Explode article ids
+               $data['a_ids'] = explode("\n", $data['a_ids']);
+
+               //Explode article descriptions
+               $data['a_descriptions'] = explode("\n", $data['a_descriptions']);
+
+               //Explode article slugs
+               $data['a_slugs'] = explode("\n", $data['a_slugs']);
+
+               //Explode article titles
+               $data['a_titles'] = explode("\n", $data['a_titles']);
+
+               //Iterate on articles
+               foreach($data['a_ids'] as $k => $aid) {
+                       $return['articles'][$aid] = [
+                               'id' => $aid,
+                               'description' => $data['a_descriptions'][$k],
+                               'slug' => $slug = $data['a_slugs'][$k],
+                               'title' => $data['a_titles'][$k],
+                               'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $aid, 'slug' => $slug]),
+                               'keywords' => []
+                       ];
+               }
+
+               //Explode article ids
+               $data['ak_aids'] = explode("\n", $data['ak_aids']);
+
+               //Explode article keyword ids
+               $data['ak_kids'] = explode("\n", $data['ak_kids']);
+
+               //Explode article keyword slugs
+               $data['ak_slugs'] = explode("\n", $data['ak_slugs']);
+
+               //Explode article keyword titles
+               $data['ak_titles'] = explode("\n", $data['ak_titles']);
+
+               //Iterate on articles
+               foreach($data['ak_aids'] as $k => $aid) {
+                       $return['articles'][$aid]['keywords'][$data['ak_kids'][$k]] = [
+                               'id' => $data['ak_kids'][$k],
+                               'slug' => $slug = $data['ak_slugs'][$k],
+                               'title' => $data['ak_titles'][$k],
+                               'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $data['ak_kids'][$k], 'slug' => $slug]),
+                       ];
+               }
+
+               //Explode alternate locales
+               $data['ckt_locales'] = explode("\n", $data['ckt_locales']);
+
+               //Explode alternate slugs
+               $data['ckt_slugs'] = explode("\n", $data['ckt_slugs']);
+
+               //Explode alternate titles
+               $data['ckt_titles'] = explode("\n", $data['ckt_titles']);
+
+               foreach($data['ckt_locales'] as $k => $locale) {
+                       $return['alternates'][$locale] = [
+                               'locale' => $locale,
+                               'slug' => $slug = $data['ckt_slugs'][$k],
+                               'title' => $data['ckt_titles'][$k],
+                               'link' => $this->router->generate('rapsys_blog_keyword_view', ['_locale' => $locale, 'id' => $id, 'slug' => $slug]),
+                       ];

                }
                }

-               //Send result
-               return $ret;
+
+               //Return return
+               return $return;

        }
 }
        }
 }