From ba6ed1d2d45b58011d275ef5960fdb012b9d0b94 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Rapha=C3=ABl=20Gertz?= Date: Sun, 25 Feb 2018 06:04:38 +0100 Subject: [PATCH] Add repository classes --- Repository/ArticleRepository.php | 101 ++++++++++++++++++++ Repository/ArticleTranslationRepository.php | 13 +++ Repository/AuthorRepository.php | 13 +++ Repository/KeywordRepository.php | 70 ++++++++++++++ Repository/KeywordTranslationRepository.php | 13 +++ Repository/LanguageRepository.php | 13 +++ Repository/SiteRepository.php | 13 +++ Repository/SiteTranslationRepository.php | 13 +++ 8 files changed, 249 insertions(+) create mode 100644 Repository/ArticleRepository.php create mode 100644 Repository/ArticleTranslationRepository.php create mode 100644 Repository/AuthorRepository.php create mode 100644 Repository/KeywordRepository.php create mode 100644 Repository/KeywordTranslationRepository.php create mode 100644 Repository/LanguageRepository.php create mode 100644 Repository/SiteRepository.php create mode 100644 Repository/SiteTranslationRepository.php diff --git a/Repository/ArticleRepository.php b/Repository/ArticleRepository.php new file mode 100644 index 0000000..9dd1556 --- /dev/null +++ b/Repository/ArticleRepository.php @@ -0,0 +1,101 @@ +addScalarResult('id', 'id'); +# $rsm->addScalarResult('created', 'created'); +# $rsm->addScalarResult('updated', 'updated'); +# $rsm->addScalarResult('slug', 'slug'); +# $rsm->addScalarResult('title', 'title'); +# $rsm->addScalarResult('description', 'description'); +# $rsm->addScalarResult('body', 'body'); +# $rsm->addScalarResult('keywords', 'keywords'); +# $rsm->addScalarResult('slugs', 'slugs'); +# +# //Fetch article +# $ret = $this->getEntityManager() +# #->createQuery('SELECT b.*, JSON(bl.iso6391, bat.slug) AS slugs FROM (SELECT a.id, a.created, a.updated, at.slug, at.title, at.description, at.body, JSON(kt.slug, kt.title) AS keywords FROM RapsysBlogBundle:Article a JOIN RapsysBlogBundle:ArticleTranslation at WITH (at.article_id = a.id AND at.slug = :_article) JOIN RapsysBlogBundle:Language AS l WITH (l.id = at.language_id AND l.iso6391 = :_locale) LEFT JOIN RapsysBlogBundle:ArticleKeyword ak WITH (ak.article_id = a.id) LEFT JOIN RapsysBlogBundle:KeywordTranslation kt WITH (kt.keyword_id = ak.keyword_id AND kt.language_id = at.language_id) GROUP BY a.id) b LEFT JOIN RapsysBlogBundle:Language AS bl WITH (bl.iso6391 <> :_locale) LEFT JOIN RapsysBlogBundle:ArticleTranslation bat WITH (bat.language_id = bl.id AND bat.article_id = b.id) GROUP BY b.id') +# ->createNativeQuery('SELECT b.*, CONCAT(\'{\', GROUP_CONCAT(CONCAT(\'"\', REPLACE(bl.iso6391, \'"\', \'\\\\"\'), \'": "\', REPLACE(bat.slug, \'"\', \'\\\\"\'), \'"\')), \'}\') AS slugs FROM (SELECT a.id, a.created, a.updated, at.slug, at.title, at.description, at.body, CONCAT(\'{\', GROUP_CONCAT(CONCAT(\'"\', REPLACE(kt.slug, \'"\', \'\\\\"\'), \'": "\', REPLACE(kt.title, \'"\', \'\\\\"\'), \'"\')), \'}\') AS keywords FROM articles a JOIN articles_translations at ON (at.article_id = a.id AND at.slug = ?) JOIN languages AS l ON (l.id = at.language_id AND l.iso6391 = ?) LEFT JOIN articles_keywords ak ON (ak.article_id = a.id) LEFT JOIN keywords_translations kt ON (kt.keyword_id = ak.keyword_id AND kt.language_id = at.language_id) GROUP BY a.id) b LEFT JOIN languages AS bl ON (bl.iso6391 <> ?) LEFT JOIN articles_translations bat ON (bat.language_id = bl.id AND bat.article_id = b.id) GROUP BY b.id', $rsm) +# ->setParameter(0, $_article) +# ->setParameter(1, $_locale) +# ->setParameter(2, $_locale) +# ->getSingleResult(); +# //Decode json +# if (!empty($ret['keywords'])) { +# $ret['keywords'] = json_decode($ret['keywords'], true); +# } +# if (!empty($ret['slugs'])) { +# $ret['slugs'] = json_decode($ret['slugs'], true); +# } +# //Send result +# return $ret; +# + //Fetch article + $ret = $this->getEntityManager() + #->createQuery('SELECT b.*, JSON(bl.iso6391, bat.slug) AS slugs FROM (SELECT a.id, a.created, a.updated, at.slug, at.title, at.description, at.body, JSON(kt.slug, kt.title) AS keywords FROM RapsysBlogBundle:Article a JOIN RapsysBlogBundle:ArticleTranslation at WITH (at.article_id = a.id AND at.slug = :_article) JOIN RapsysBlogBundle:Language AS l WITH (l.id = at.language_id AND l.iso6391 = :_locale) LEFT JOIN RapsysBlogBundle:ArticleKeyword ak WITH (ak.article_id = a.id) LEFT JOIN RapsysBlogBundle:KeywordTranslation kt WITH (kt.keyword_id = ak.keyword_id AND kt.language_id = at.language_id) GROUP BY a.id) b LEFT JOIN RapsysBlogBundle:Language AS bl WITH (bl.iso6391 <> :_locale) LEFT JOIN RapsysBlogBundle:ArticleTranslation bat WITH (bat.language_id = bl.id AND bat.article_id = b.id) GROUP BY b.id') + ->createQuery('SELECT a.id, a.created, a.updated, at.slug, at.title, at.description, at.body, JSON(kt.slug, kt.title) AS keywords FROM RapsysBlogBundle:Article a JOIN RapsysBlogBundle:ArticleTranslation at WITH (at.article_id = a.id AND at.slug = :_article) JOIN RapsysBlogBundle:Language AS l WITH (l.id = at.language_id AND l.iso6391 = :_locale) LEFT JOIN RapsysBlogBundle:ArticleKeyword ak WITH (ak.article_id = a.id) LEFT JOIN RapsysBlogBundle:KeywordTranslation kt WITH (kt.keyword_id = ak.keyword_id AND kt.language_id = at.language_id) GROUP BY a.id') + ->setParameter('_locale', $_locale) + ->setParameter('_article', $_article) + ->getSingleResult(); + + //Decode json + if (!empty($ret['keywords'])) { + $ret['keywords'] = json_decode($ret['keywords'], true); + } + + //Fetch article's slugs in other locale + $slugs = $this->getEntityManager() + ->createQuery('SELECT JSON(bl.iso6391, bat.slug) AS slugs FROM RapsysBlogBundle:Language bl LEFT JOIN RapsysBlogBundle:ArticleTranslation bat WITH (bat.article_id = :_article AND bat.language_id = bl.id) WHERE (bl.iso6391 <> :_locale) GROUP BY bat.article_id') + ->setParameter('_locale', $_locale) + ->setParameter('_article', $ret['id']) + ->getSingleResult(); + + //Decode json + if (!empty($slugs['slugs'])) { + $ret['slugs'] = json_decode($slugs['slugs'], true); + } + + //Send result + return $ret; + } + + /** + * Find articles + * + * @param string $_locale + */ + public function findArticles($_locale) { + //Fetch articles + $ret = $this->getEntityManager() + ->createQuery('SELECT a.id, a.created, a.updated, at.slug, at.title, at.description, JSON(kt.slug, kt.title) AS keywords FROM RapsysBlogBundle:Article a JOIN RapsysBlogBundle:ArticleTranslation at WITH (at.article_id = a.id) JOIN RapsysBlogBundle:Language AS l WITH (l.id = at.language_id AND l.iso6391 = :_locale) LEFT JOIN RapsysBlogBundle:ArticleKeyword ak WITH (ak.article_id = a.id) LEFT JOIN RapsysBlogBundle:KeywordTranslation kt WITH (kt.keyword_id = ak.keyword_id AND kt.language_id = at.language_id) GROUP BY a.id') + ->setParameter('_locale', $_locale) + ->execute(); + //Decode json + if (!empty($ret)) { + foreach ($ret as $id => $article) { + if (!empty($article['keywords'])) { + $ret[$id]['keywords'] = json_decode($article['keywords'], true); + } + } + } + //Send result + return $ret; + } +} diff --git a/Repository/ArticleTranslationRepository.php b/Repository/ArticleTranslationRepository.php new file mode 100644 index 0000000..f93e82e --- /dev/null +++ b/Repository/ArticleTranslationRepository.php @@ -0,0 +1,13 @@ +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); + } + + //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(); + + //Decode json + if (!empty($slugs['slugs'])) { + $ret['slugs'] = json_decode($slugs['slugs'], true); + } + + //Send result + return $ret; + } + + /** + * Find keywords + * + * @param string $_locale + */ + 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); + } + } + } + //Send result + return $ret; + } +} diff --git a/Repository/KeywordTranslationRepository.php b/Repository/KeywordTranslationRepository.php new file mode 100644 index 0000000..be5a1b3 --- /dev/null +++ b/Repository/KeywordTranslationRepository.php @@ -0,0 +1,13 @@ +