]> Raphaël G. Git Repositories - blogbundle/blob - Repository/ArticleRepository.php
Add findByIdAsArray function
[blogbundle] / Repository / ArticleRepository.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 * ArticleRepository
18 *
19 * This class was generated by the Doctrine ORM. Add your own custom
20 * repository methods below.
21 */
22 class ArticleRepository extends EntityRepository {
23 /**
24 * Find article count as int
25 *
26 * @return integer The articles count
27 */
28 public function findCountAsInt(): int {
29 //Set the request
30 $req = <<<SQL
31 SELECT COUNT(at.article_id) AS count
32 FROM RapsysBlogBundle:ArticleTranslation AS at
33 WHERE at.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 articles as array
56 *
57 * @param integer $page The page
58 * @param integer $count The count
59 * @return array The articles array
60 */
61 public function findAllAsArray(int $page, int $count): array {
62 //Set the request
63 $req = <<<SQL
64 SELECT
65 a.id,
66 GREATEST(a.created, at.created, k.created, kt.created) AS created,
67 GREATEST(a.updated, at.updated, k.updated, kt.updated) AS updated,
68 GREATEST(a.created, at.created, k.created, kt.created, a.updated, at.updated, k.updated, kt.updated) as modified,
69 at.body,
70 at.description,
71 at.slug,
72 at.title,
73 GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
74 GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
75 GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
76 FROM RapsysBlogBundle:Article AS a
77 JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
78 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
79 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
80 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
81 GROUP BY a.id
82 ORDER BY updated, created DESC
83 LIMIT :offset, :count
84 SQL;
85
86 //Replace bundle entity name by table name
87 $req = $this->replace($req);
88
89 //Get result set mapping instance
90 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
91 $rsm = new ResultSetMapping();
92
93 //Declare all fields
94 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
95 //addScalarResult($sqlColName, $resColName, $type = 'string');
96 $rsm->addScalarResult('id', 'id', 'integer')
97 ->addScalarResult('body', 'body', 'string')
98 ->addScalarResult('description', 'description', 'string')
99 ->addScalarResult('slug', 'slug', 'string')
100 ->addScalarResult('title', 'title', 'string')
101 ->addScalarResult('k_ids', 'k_ids', 'string')
102 ->addScalarResult('k_slugs', 'k_slugs', 'string')
103 ->addScalarResult('k_titles', 'k_titles', 'string')
104 ->addScalarResult('created', 'created', 'datetime')
105 ->addScalarResult('updated', 'updated', 'datetime')
106 ->addScalarResult('modified', 'modified', 'datetime')
107 ->addIndexByScalar('id');
108
109 //Get result
110 $result = $this->_em
111 ->createNativeQuery($req, $rsm)
112 ->setParameter('offset', $page * $count)
113 ->setParameter('count', $count)
114 ->getArrayResult();
115
116 //Set return
117 $return = [];
118
119 //Iterate on each city
120 foreach($result as $data) {
121 //Add to return
122 $return[$data['id']] = [
123 'id' => $id = $data['id'],
124 'body' => $data['body'],
125 'description' => $data['description'],
126 'slug' => $slug = $data['slug'],
127 'title' => $data['title'],
128 'created' => $data['created'],
129 'updated' => $data['updated'],
130 'modified' => $data['modified'],
131 'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
132 'keywords' => []
133 ];
134
135 //Explode keyword ids
136 $data['k_ids'] = explode("\n", $data['k_ids']);
137
138 //Explode keyword slugs
139 $data['k_slugs'] = explode("\n", $data['k_slugs']);
140
141 //Explode keyword titles
142 $data['k_titles'] = explode("\n", $data['k_titles']);
143
144 foreach($data['k_ids'] as $k => $id) {
145 $return[$data['id']]['keywords'][$id] = [
146 'id' => $id,
147 'slug' => $slug = $data['k_slugs'][$k],
148 'title' => $data['k_titles'][$k],
149 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $id, 'slug' => $slug]),
150 ];
151 }
152 }
153
154 //Return return
155 return $return;
156 }
157
158 /**
159 * Find article by id as array
160 *
161 * @param integer $id The article id
162 * @return array The article array
163 */
164 public function findByIdAsArray(int $id): array {
165 //Set the request
166 $req = <<<SQL
167 SELECT
168 b.id,
169 b.created,
170 b.updated,
171 b.modified,
172 b.body,
173 b.description,
174 b.slug,
175 b.title,
176 b.k_ids,
177 b.k_slugs,
178 b.k_titles,
179 GROUP_CONCAT(kat.locale ORDER BY kat.locale SEPARATOR "\\n") AS kat_locales,
180 GROUP_CONCAT(kat.slug ORDER BY kat.locale SEPARATOR "\\n") AS kat_slugs,
181 GROUP_CONCAT(kat.title ORDER BY kat.locale SEPARATOR "\\n") AS kat_titles
182 FROM (
183 SELECT
184 a.id,
185 GREATEST(a.created, at.created, k.created, kt.created) AS created,
186 GREATEST(a.updated, at.updated, k.updated, kt.updated) AS updated,
187 GREATEST(a.created, at.created, k.created, kt.created, a.updated, at.updated, k.updated, kt.updated) AS modified,
188 at.body,
189 at.description,
190 at.slug,
191 at.title,
192 GROUP_CONCAT(k.id ORDER BY k.id SEPARATOR "\\n") AS k_ids,
193 GROUP_CONCAT(kt.slug ORDER BY k.id SEPARATOR "\\n") AS k_slugs,
194 GROUP_CONCAT(kt.title ORDER BY k.id SEPARATOR "\\n") AS k_titles
195 FROM RapsysBlogBundle:Article AS a
196 JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
197 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
198 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
199 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = k.id AND kt.locale = :locale)
200 WHERE a.id = :id
201 GROUP BY a.id
202 ORDER BY NULL
203 LIMIT 0, :limit
204 ) AS b
205 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS kat ON (kat.article_id = b.id AND kat.locale <> :locale)
206 GROUP BY b.id
207 SQL;
208
209 //Replace bundle entity name by table name
210 $req = $this->replace($req);
211
212 //Get result set mapping instance
213 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
214 $rsm = new ResultSetMapping();
215
216 //Declare all fields
217 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
218 //addScalarResult($sqlColName, $resColName, $type = 'string');
219 $rsm->addScalarResult('id', 'id', 'integer')
220 ->addScalarResult('body', 'body', 'string')
221 ->addScalarResult('description', 'description', 'string')
222 ->addScalarResult('slug', 'slug', 'string')
223 ->addScalarResult('title', 'title', 'string')
224 ->addScalarResult('k_ids', 'k_ids', 'string')
225 ->addScalarResult('k_slugs', 'k_slugs', 'string')
226 ->addScalarResult('k_titles', 'k_titles', 'string')
227 ->addScalarResult('kat_locales', 'kat_locales', 'string')
228 ->addScalarResult('kat_slugs', 'kat_slugs', 'string')
229 ->addScalarResult('kat_titles', 'kat_titles', 'string')
230 ->addScalarResult('created', 'created', 'datetime')
231 ->addScalarResult('updated', 'updated', 'datetime')
232 ->addScalarResult('modified', 'modified', 'datetime');
233
234 //Get data
235 $data = $this->_em
236 ->createNativeQuery($req, $rsm)
237 ->setParameter('id', $id)
238 ->getOneOrNullResult();
239
240 //Set return
241 $return = [
242 'id' => $id = $data['id'],
243 'body' => $data['body'],
244 'description' => $data['description'],
245 'slug' => $slug = $data['slug'],
246 'title' => $data['title'],
247 'created' => $data['created'],
248 'updated' => $data['updated'],
249 'modified' => $data['modified'],
250 'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slug]),
251 'alternates' => [],
252 'keywords' => []
253 ];
254
255 //Explode keyword ids
256 $data['k_ids'] = explode("\n", $data['k_ids']);
257
258 //Explode keyword slugs
259 $data['k_slugs'] = explode("\n", $data['k_slugs']);
260
261 //Explode keyword titles
262 $data['k_titles'] = explode("\n", $data['k_titles']);
263
264 foreach($data['k_ids'] as $k => $kid) {
265 $return['keywords'][$kid] = [
266 'id' => $kid,
267 'slug' => $slug = $data['k_slugs'][$k],
268 'title' => $data['k_titles'][$k],
269 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slug]),
270 ];
271 }
272
273 //Explode alternate locales
274 $data['kat_locales'] = explode("\n", $data['kat_locales']);
275
276 //Explode alternate slugs
277 $data['kat_slugs'] = explode("\n", $data['kat_slugs']);
278
279 //Explode alternate titles
280 $data['kat_titles'] = explode("\n", $data['kat_titles']);
281
282 foreach($data['kat_locales'] as $k => $locale) {
283 $return['alternates'][$locale] = [
284 'locale' => $locale,
285 'slug' => $slug = $data['kat_slugs'][$k],
286 'title' => $data['kat_titles'][$k],
287 'link' => $this->router->generate('rapsys_blog_article_view', ['_locale' => $locale, 'id' => $id, 'slug' => $slug]),
288 ];
289 }
290
291 //Return return
292 return $return;
293 }
294 }