Raphaël G. Git Repositories - blogbundle/blob - Repository/KeywordRepository.php

   1 <?php
   2
   3 namespace Rapsys\BlogBundle\Repository;
   4
   5 /**
   6  * KeywordRepository
   7  *
   8  * This class was generated by the Doctrine ORM. Add your own custom
   9  * repository methods below.
  10  */
  11 class KeywordRepository extends \Doctrine\ORM\EntityRepository {
  12         /**
  13          * Find keyword
  14          *
  15          * @param string $_locale
  16          * @param string $_keyword
  17          */
  18         public function findKeyword($_locale, $_keyword) {
  19                 //Fetch keyword
  20                 $ret = $this->getEntityManager()
  21                         ->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')
  22                         ->setParameter('_locale', $_locale)
  23                         ->setParameter('_keyword', $_keyword)
  24                         ->getSingleResult();
  25
  26                 //Decode json
  27                 if (!empty($ret['articles'])) {
  28                         $ret['articles'] = json_decode($ret['articles'], true);
  29                 }
  30
  31                 //Fetch keyword's slugs in other locale
  32                 $slugs = $this->getEntityManager()
  33                         ->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')
  34                         ->setParameter('_locale', $_locale)
  35                         ->setParameter('_keyword', $ret['id'])
  36                         ->getSingleResult();
  37
  38                 //Decode json
  39                 if (!empty($slugs['slugs'])) {
  40                         $ret['slugs'] = json_decode($slugs['slugs'], true);
  41                 }
  42
  43                 //Send result
  44                 return $ret;
  45         }
  46
  47         /**
  48          * Find keywords
  49          *
  50          * @param string $_locale
  51          */
  52         public function findKeywords($_locale) {
  53                 //Fetch keywords
  54                 $ret = $this->getEntityManager()
  55                         #, JSON(kt.keyword_id, kt.title) AS keywords
  56                         ->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')
  57                         ->setParameter('_locale', $_locale)
  58                         ->execute();
  59                 //Decode json
  60                 if (!empty($ret)) {
  61                         foreach ($ret as $id => $keyword) {
  62                                 if (!empty($keyword['articles'])) {
  63                                         $ret[$id]['articles'] = json_decode($keyword['articles'], true);
  64                                 }
  65                         }
  66                 }
  67                 //Send result
  68                 return $ret;
  69         }
  70 }