1 <?php
declare(strict_types
=1);
4 * This file is part of the Rapsys BlogBundle package.
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
12 namespace Rapsys\BlogBundle\Repository
;
14 use Doctrine\ORM\Query\ResultSetMapping
;
19 * This class was generated by the Doctrine ORM. Add your own custom
20 * repository methods below.
22 class KeywordRepository
extends EntityRepository
{
24 * Find keyword count as int
26 * @return integer The keywords count
28 public function findCountAsInt(): int {
31 SELECT COUNT(kt.keyword_id) AS count
32 FROM RapsysBlogBundle:KeywordTranslation AS kt
33 WHERE kt.locale = :locale
36 //Get result set mapping instance
37 $req = $this->replace($req);
39 //Get result set mapping instance
40 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
41 $rsm = new ResultSetMapping();
44 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
45 //addScalarResult($sqlColName, $resColName, $type = 'string');
46 $rsm->addScalarResult('count', 'count', 'integer');
50 ->createNativeQuery($req, $rsm)
51 ->getSingleScalarResult();
55 * Find keywords as array
57 * @param integer $page The page
58 * @param integer $count The count
59 * @return array The keywords array
61 public function findAllAsArray(int $page, int $count): array {
66 GREATEST(k.created, kt.created) AS created,
67 GREATEST(k.updated, kt.updated) AS updated,
68 GREATEST(k.created, kt.created, k.updated, kt.updated) AS modified,
72 FROM RapsysBlogBundle:Keyword AS k
73 JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
75 ORDER BY updated, created DESC, k.id
79 //Replace bundle entity name by table name
80 $req = $this->replace($req);
82 //Get result set mapping instance
83 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
84 $rsm = new ResultSetMapping();
87 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
88 //addScalarResult($sqlColName, $resColName, $type = 'string');
89 $rsm->addScalarResult('id', 'id', 'integer')
90 ->addScalarResult('description', 'description', 'string')
91 ->addScalarResult('slug', 'slug', 'string')
92 ->addScalarResult('title', 'title', 'string')
93 ->addScalarResult('created', 'created', 'datetime')
94 ->addScalarResult('updated', 'updated', 'datetime')
95 ->addScalarResult('modified', 'modified', 'datetime')
96 ->addIndexByScalar('id');
100 ->createNativeQuery($req, $rsm)
101 ->setParameter('offset', $page * $count)
102 ->setParameter('count', $count)
108 //Iterate on each city
109 foreach($result as $data) {
111 $return[$data['id']] = [
112 'id' => $id = $data['id'],
113 'description' => $data['description'],
114 'slug' => $slug = $data['slug'],
115 'title' => $data['title'],
116 'created' => $data['created'],
117 'updated' => $data['updated'],
118 'modified' => $data['modified'],
119 'link' => $this->router
->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug])
128 * Find keyword by id as array
130 * @param integer $id The keyword id
131 * @return array The keyword array
133 public function findByIdAsArray(int $id): array {
152 GROUP_CONCAT(ckt.locale ORDER BY ckt.locale SEPARATOR "\\n") AS ckt_locales,
153 GROUP_CONCAT(ckt.slug ORDER BY ckt.locale SEPARATOR "\\n") AS ckt_slugs,
154 GROUP_CONCAT(ckt.title ORDER BY ckt.locale SEPARATOR "\\n") AS ckt_titles
160 GREATEST(b.modified, k.created, k.updated) AS modified,
168 GROUP_CONCAT(ak2.article_id ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_aids,
169 GROUP_CONCAT(ak2.keyword_id ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_kids,
170 GROUP_CONCAT(kt.slug ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_slugs,
171 GROUP_CONCAT(kt.title ORDER BY ak2.article_id, ak2.keyword_id SEPARATOR "\\n") AS ak_titles
175 GREATEST(k.created, kt.created) AS created,
176 GREATEST(k.updated, kt.updated) AS updated,
177 GREATEST(k.created, kt.created, a.created, at.created, k.updated, kt.updated, a.updated, at.updated) AS modified,
181 GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR "\\n") AS a_ids,
182 GROUP_CONCAT(at.description ORDER BY a.id SEPARATOR "\\n") AS a_descriptions,
183 GROUP_CONCAT(at.slug ORDER BY a.id SEPARATOR "\\n") AS a_slugs,
184 GROUP_CONCAT(at.title ORDER BY a.id SEPARATOR "\\n") AS a_titles
185 FROM RapsysBlogBundle:Keyword AS k
186 JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
187 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.keyword_id = k.id)
188 LEFT JOIN RapsysBlogBundle:Article AS a ON (a.id = ak.article_id)
189 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
195 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.keyword_id = b.id)
196 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak2 ON (ak2.article_id = ak.article_id)
197 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak2.keyword_id)
198 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak2.keyword_id AND kt.locale = :locale)
203 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS ckt ON (ckt.keyword_id = c.id AND ckt.locale <> :locale)
207 //Replace bundle entity name by table name
208 $req = $this->replace($req);
210 //Get result set mapping instance
211 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
212 $rsm = new ResultSetMapping();
215 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
216 //addScalarResult($sqlColName, $resColName, $type = 'string');
217 $rsm->addScalarResult('id', 'id', 'integer')
218 ->addScalarResult('body', 'body', 'string')
219 ->addScalarResult('description', 'description', 'string')
220 ->addScalarResult('slug', 'slug', 'string')
221 ->addScalarResult('title', 'title', 'string')
222 ->addScalarResult('a_ids', 'a_ids', 'string')
223 ->addScalarResult('a_descriptions', 'a_descriptions', 'string')
224 ->addScalarResult('a_slugs', 'a_slugs', 'string')
225 ->addScalarResult('a_titles', 'a_titles', 'string')
226 ->addScalarResult('ak_aids', 'ak_aids', 'string')
227 ->addScalarResult('ak_kids', 'ak_kids', 'string')
228 ->addScalarResult('ak_slugs', 'ak_slugs', 'string')
229 ->addScalarResult('ak_titles', 'ak_titles', 'string')
230 ->addScalarResult('ckt_locales', 'ckt_locales', 'string')
231 ->addScalarResult('ckt_slugs', 'ckt_slugs', 'string')
232 ->addScalarResult('ckt_titles', 'ckt_titles', 'string')
233 ->addScalarResult('created', 'created', 'datetime')
234 ->addScalarResult('updated', 'updated', 'datetime')
235 ->addScalarResult('modified', 'modified', 'datetime');
239 ->createNativeQuery($req, $rsm)
240 ->setParameter('id', $id)
241 ->getOneOrNullResult();
245 'id' => $id = $data['id'],
246 'description' => $data['description'],
247 'slug' => $slug = $data['slug'],
248 'title' => $data['title'],
249 'created' => $data['created'],
250 'updated' => $data['updated'],
251 'modified' => $data['modified'],
252 'link' => $this->router
->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug]),
257 //Explode article ids
258 $data['a_ids'] = explode("\n", $data['a_ids']);
260 //Explode article descriptions
261 $data['a_descriptions'] = explode("\n", $data['a_descriptions']);
263 //Explode article slugs
264 $data['a_slugs'] = explode("\n", $data['a_slugs']);
266 //Explode article titles
267 $data['a_titles'] = explode("\n", $data['a_titles']);
269 //Iterate on articles
270 foreach($data['a_ids'] as $k => $aid) {
271 $return['articles'][$aid] = [
273 'description' => $data['a_descriptions'][$k],
274 'slug' => $slug = $data['a_slugs'][$k],
275 'title' => $data['a_titles'][$k],
276 'link' => $this->router
->generate('rapsys_blog_article_view', ['id' => $aid, 'slug' => $slug]),
281 //Explode article ids
282 $data['ak_aids'] = explode("\n", $data['ak_aids']);
284 //Explode article keyword ids
285 $data['ak_kids'] = explode("\n", $data['ak_kids']);
287 //Explode article keyword slugs
288 $data['ak_slugs'] = explode("\n", $data['ak_slugs']);
290 //Explode article keyword titles
291 $data['ak_titles'] = explode("\n", $data['ak_titles']);
293 //Iterate on articles
294 foreach($data['ak_aids'] as $k => $aid) {
295 $return['articles'][$aid]['keywords'][$data['ak_kids'][$k]] = [
296 'id' => $data['ak_kids'][$k],
297 'slug' => $slug = $data['ak_slugs'][$k],
298 'title' => $data['ak_titles'][$k],
299 'link' => $this->router
->generate('rapsys_blog_keyword_view', ['id' => $data['ak_kids'][$k], 'slug' => $slug]),
303 //Explode alternate locales
304 $data['ckt_locales'] = explode("\n", $data['ckt_locales']);
306 //Explode alternate slugs
307 $data['ckt_slugs'] = explode("\n", $data['ckt_slugs']);
309 //Explode alternate titles
310 $data['ckt_titles'] = explode("\n", $data['ckt_titles']);
312 foreach($data['ckt_locales'] as $k => $locale) {
313 $return['alternates'][$locale] = [
315 'slug' => $slug = $data['ckt_slugs'][$k],
316 'title' => $data['ckt_titles'][$k],
317 'link' => $this->router
->generate('rapsys_blog_keyword_view', ['_locale' => $locale, 'id' => $id, 'slug' => $slug]),