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

   1 <?php
   2
   3 namespace Rapsys\BlogBundle\Repository;
   4
   5 /**
   6  * ArticleRepository
   7  *
   8  * This class was generated by the Doctrine ORM. Add your own custom
   9  * repository methods below.
  10  */
  11 class ArticleRepository extends \Doctrine\ORM\EntityRepository {
  12         /**
  13          * Find article
  14          *
  15          * @param string $_locale
  16          * @param string $_article
  17          */
  18         public function findArticle($_locale, $_article) {
  19 #               //TODO: voir comment faire ça sans gros hack sql ?
  20 #               //Declare all field
  21 #               $rsm = new \Doctrine\ORM\Query\ResultSetMapping();
  22 #               $rsm->addScalarResult('id', 'id');
  23 #               $rsm->addScalarResult('created', 'created');
  24 #               $rsm->addScalarResult('updated', 'updated');
  25 #               $rsm->addScalarResult('slug', 'slug');
  26 #               $rsm->addScalarResult('title', 'title');
  27 #               $rsm->addScalarResult('description', 'description');
  28 #               $rsm->addScalarResult('body', 'body');
  29 #               $rsm->addScalarResult('keywords', 'keywords');
  30 #               $rsm->addScalarResult('slugs', 'slugs');
  31 #
  32 #               //Fetch article
  33 #               $ret = $this->getEntityManager()
  34 #                       #->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')
  35 #                       ->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)
  36 #                       ->setParameter(0, $_article)
  37 #                       ->setParameter(1, $_locale)
  38 #                       ->setParameter(2, $_locale)
  39 #                       ->getSingleResult();
  40 #               //Decode json
  41 #               if (!empty($ret['keywords'])) {
  42 #                       $ret['keywords'] = json_decode($ret['keywords'], true);
  43 #               }
  44 #               if (!empty($ret['slugs'])) {
  45 #                       $ret['slugs'] = json_decode($ret['slugs'], true);
  46 #               }
  47 #               //Send result
  48 #               return $ret;
  49 #
  50                 //Fetch article
  51                 $ret = $this->getEntityManager()
  52                         #->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')
  53                         ->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')
  54                         ->setParameter('_locale', $_locale)
  55                         ->setParameter('_article', $_article)
  56                         ->getSingleResult();
  57
  58                 //Decode json
  59                 if (!empty($ret['keywords'])) {
  60                         $ret['keywords'] = json_decode($ret['keywords'], true);
  61                 }
  62
  63                 //Fetch article's slugs in other locale
  64                 $slugs = $this->getEntityManager()
  65                         ->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')
  66                         ->setParameter('_locale', $_locale)
  67                         ->setParameter('_article', $ret['id'])
  68                         ->getSingleResult();
  69
  70                 //Decode json
  71                 if (!empty($slugs['slugs'])) {
  72                         $ret['slugs'] = json_decode($slugs['slugs'], true);
  73                 }
  74
  75                 //Send result
  76                 return $ret;
  77         }
  78
  79         /**
  80          * Find articles
  81          *
  82          * @param string $_locale
  83          */
  84         public function findArticles($_locale) {
  85                 //Fetch articles
  86                 $ret = $this->getEntityManager()
  87                         ->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')
  88                         ->setParameter('_locale', $_locale)
  89                         ->execute();
  90                 //Decode json
  91                 if (!empty($ret)) {
  92                         foreach ($ret as $id => $article) {
  93                                 if (!empty($article['keywords'])) {
  94                                         $ret[$id]['keywords'] = json_decode($article['keywords'], true);
  95                                 }
  96                         }
  97                 }
  98                 //Send result
  99                 return $ret;
 100         }
 101 }