From d35d293c7ae158d46716f3d98ce96d259114cb90 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Rapha=C3=ABl=20Gertz?= Date: Fri, 10 Nov 2023 13:14:46 +0100 Subject: [PATCH] Strict types Remove old member functions Inherit entity repository Add find count, keywords and keyword member functions --- Repository/KeywordRepository.php | 346 +++++++++++++++++++++++++++---- 1 file changed, 300 insertions(+), 46 deletions(-) diff --git a/Repository/KeywordRepository.php b/Repository/KeywordRepository.php index 08e0d22..f531711 100644 --- a/Repository/KeywordRepository.php +++ b/Repository/KeywordRepository.php @@ -1,70 +1,324 @@ - + * + * 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 = <<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 = <<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 = << :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; } } -- 2.41.1