]> Raphaƫl G. Git Repositories - blogbundle/blob - Repository/KeywordRepository.php
Add favicon
[blogbundle] / 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 }