]> Raphaël G. Git Repositories - blogbundle/blob - Repository/KeywordRepository.php
Use entity id as last resort sorting parameter
[blogbundle] / Repository / KeywordRepository.php
1 <?php declare(strict_types=1);
2
3 /*
4 * This file is part of the Rapsys BlogBundle package.
5 *
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
7 *
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
10 */
11
12 namespace Rapsys\BlogBundle\Repository;
13
14 use Doctrine\ORM\Query\ResultSetMapping;
15
16 /**
17 * KeywordRepository
18 *
19 * This class was generated by the Doctrine ORM. Add your own custom
20 * repository methods below.
21 */
22 class KeywordRepository extends EntityRepository {
23 /**
24 * Find keyword count as int
25 *
26 * @return integer The keywords count
27 */
28 public function findCountAsInt(): int {
29 //Set the request
30 $req = <<<SQL
31 SELECT COUNT(kt.keyword_id) AS count
32 FROM RapsysBlogBundle:KeywordTranslation AS kt
33 WHERE kt.locale = :locale
34 SQL;
35
36 //Get result set mapping instance
37 $req = $this->replace($req);
38
39 //Get result set mapping instance
40 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
41 $rsm = new ResultSetMapping();
42
43 //Declare all fields
44 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
45 //addScalarResult($sqlColName, $resColName, $type = 'string');
46 $rsm->addScalarResult('count', 'count', 'integer');
47
48 //Get result
49 return $this->_em
50 ->createNativeQuery($req, $rsm)
51 ->getSingleScalarResult();
52 }
53
54 /**
55 * Find keywords as array
56 *
57 * @param integer $page The page
58 * @param integer $count The count
59 * @return array The keywords array
60 */
61 public function findAllAsArray(int $page, int $count): array {
62 //Set the request
63 $req = <<<SQL
64 SELECT
65 k.id,
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,
69 kt.description,
70 kt.slug,
71 kt.title
72 FROM RapsysBlogBundle:Keyword AS k
73 JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
74 GROUP BY k.id
75 ORDER BY updated, created DESC, k.id
76 LIMIT :offset, :count
77 SQL;
78
79 //Replace bundle entity name by table name
80 $req = $this->replace($req);
81
82 //Get result set mapping instance
83 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
84 $rsm = new ResultSetMapping();
85
86 //Declare all fields
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');
97
98 //Get result
99 $result = $this->_em
100 ->createNativeQuery($req, $rsm)
101 ->setParameter('offset', $page * $count)
102 ->setParameter('count', $count)
103 ->getArrayResult();
104
105 //Set return
106 $return = [];
107
108 //Iterate on each city
109 foreach($result as $data) {
110 //Add to return
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])
120 ];
121 }
122
123 //Return return
124 return $return;
125 }
126
127 /**
128 * Find keyword by id as array
129 *
130 * @param integer $id The keyword id
131 * @return array The keyword array
132 */
133 public function findByIdAsArray(int $id): array {
134 //Set the request
135 $req = <<<SQL
136 SELECT
137 c.id,
138 c.created,
139 c.updated,
140 c.modified,
141 c.description,
142 c.slug,
143 c.title,
144 c.a_ids,
145 c.a_descriptions,
146 c.a_slugs,
147 c.a_titles,
148 c.ak_aids,
149 c.ak_kids,
150 c.ak_slugs,
151 c.ak_titles,
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
155 FROM (
156 SELECT
157 b.id,
158 b.created,
159 b.updated,
160 GREATEST(b.modified, k.created, k.updated) AS modified,
161 b.description,
162 b.slug,
163 b.title,
164 b.a_ids,
165 b.a_descriptions,
166 b.a_slugs,
167 b.a_titles,
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
172 FROM (
173 SELECT
174 k.id,
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,
178 kt.description,
179 kt.slug,
180 kt.title,
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)
190 WHERE k.id = :id
191 GROUP BY k.id
192 ORDER BY NULL
193 LIMIT 0, :limit
194 ) AS b
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)
199 GROUP BY b.id
200 ORDER BY NULL
201 LIMIT 0, :limit
202 ) AS c
203 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS ckt ON (ckt.keyword_id = c.id AND ckt.locale <> :locale)
204 GROUP BY c.id
205 SQL;
206
207 //Replace bundle entity name by table name
208 $req = $this->replace($req);
209
210 //Get result set mapping instance
211 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
212 $rsm = new ResultSetMapping();
213
214 //Declare all fields
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');
236
237 //Get data
238 $data = $this->_em
239 ->createNativeQuery($req, $rsm)
240 ->setParameter('id', $id)
241 ->getOneOrNullResult();
242
243 //Set return
244 $return = [
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]),
253 'alternates' => [],
254 'articles' => []
255 ];
256
257 //Explode article ids
258 $data['a_ids'] = explode("\n", $data['a_ids']);
259
260 //Explode article descriptions
261 $data['a_descriptions'] = explode("\n", $data['a_descriptions']);
262
263 //Explode article slugs
264 $data['a_slugs'] = explode("\n", $data['a_slugs']);
265
266 //Explode article titles
267 $data['a_titles'] = explode("\n", $data['a_titles']);
268
269 //Iterate on articles
270 foreach($data['a_ids'] as $k => $aid) {
271 $return['articles'][$aid] = [
272 'id' => $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]),
277 'keywords' => []
278 ];
279 }
280
281 //Explode article ids
282 $data['ak_aids'] = explode("\n", $data['ak_aids']);
283
284 //Explode article keyword ids
285 $data['ak_kids'] = explode("\n", $data['ak_kids']);
286
287 //Explode article keyword slugs
288 $data['ak_slugs'] = explode("\n", $data['ak_slugs']);
289
290 //Explode article keyword titles
291 $data['ak_titles'] = explode("\n", $data['ak_titles']);
292
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]),
300 ];
301 }
302
303 //Explode alternate locales
304 $data['ckt_locales'] = explode("\n", $data['ckt_locales']);
305
306 //Explode alternate slugs
307 $data['ckt_slugs'] = explode("\n", $data['ckt_slugs']);
308
309 //Explode alternate titles
310 $data['ckt_titles'] = explode("\n", $data['ckt_titles']);
311
312 foreach($data['ckt_locales'] as $k => $locale) {
313 $return['alternates'][$locale] = [
314 'locale' => $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]),
318 ];
319 }
320
321 //Return return
322 return $return;
323 }
324 }