-<?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;
+use Doctrine\ORM\Query\ResultSetMapping;
+
/**
* 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;
}
}
Rapsys Git / blogbundle / commitdiff