]> Raphaël G. Git Repositories - blogbundle/blob - Repository/UserRepository.php
Add doctrine dbal default charset and collate parameters
[blogbundle] / Repository / UserRepository.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 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
16 use Symfony\Component\Security\Core\User\PasswordAuthenticatedUserInterface;
17 use Symfony\Component\Security\Core\User\PasswordUpgraderInterface;
18
19 /**
20 * UserRepository
21 */
22 class UserRepository extends EntityRepository implements PasswordUpgraderInterface {
23 /**
24 * Find user 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(u.id) AS count
32 FROM RapsysBlogBundle:User AS u
33 SQL;
34
35 //Get result set mapping instance
36 $req = $this->replace($req);
37
38 //Get result set mapping instance
39 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
40 $rsm = new ResultSetMapping();
41
42 //Declare all fields
43 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
44 //addScalarResult($sqlColName, $resColName, $type = 'string');
45 $rsm->addScalarResult('count', 'count', 'integer');
46
47 //Get result
48 return $this->_em
49 ->createNativeQuery($req, $rsm)
50 ->getSingleScalarResult();
51 }
52
53 /**
54 * Find all users as array
55 *
56 * @param integer $page The page
57 * @param integer $count The count
58 * @return array The users sorted by id
59 */
60 public function findAllAsArray(int $page, int $count): array {
61 //Set the request
62 $req = <<<SQL
63 SELECT
64 t.id,
65 t.mail,
66 t.forename,
67 t.surname,
68 t.pseudonym,
69 t.slug,
70 t.created,
71 t.updated,
72 GREATEST(t.created, t.updated) AS modified,
73 t.civility,
74 t.g_ids,
75 t.g_titles,
76 GROUP_CONCAT(t.a_id ORDER BY t.a_id SEPARATOR "\\n") AS a_ids,
77 GROUP_CONCAT(t.at_description ORDER BY t.a_id SEPARATOR "\\n") AS at_descriptions,
78 GROUP_CONCAT(t.at_slug ORDER BY t.a_id SEPARATOR "\\n") AS at_slugs,
79 GROUP_CONCAT(t.at_title ORDER BY t.a_id SEPARATOR "\\n") AS at_titles,
80 GROUP_CONCAT(t.ak_ids ORDER BY t.a_id SEPARATOR "\\n") AS ak_ids,
81 GROUP_CONCAT(t.kt_slugs ORDER BY t.a_id SEPARATOR "\\n") AS kt_slugs,
82 GROUP_CONCAT(t.kt_titles ORDER BY t.a_id SEPARATOR "\\n") AS kt_titles
83 FROM (
84 SELECT
85 c.id,
86 c.mail,
87 c.forename,
88 c.surname,
89 c.pseudonym,
90 c.slug,
91 GREATEST(c.created, COALESCE(a.created, 0), COALESCE(at.created, 0), COALESCE(k.created, 0), COALESCE(kt.created, 0)) AS created,
92 GREATEST(c.updated, COALESCE(a.updated, 0), COALESCE(at.updated, 0), COALESCE(k.updated, 0), COALESCE(kt.updated, 0)) AS updated,
93 c.civility,
94 c.g_ids,
95 c.g_titles,
96 a.id AS a_id,
97 at.description AS at_description,
98 at.slug AS at_slug,
99 at.title AS at_title,
100 GROUP_CONCAT(ak.keyword_id ORDER BY ak.keyword_id SEPARATOR "\\r") AS ak_ids,
101 GROUP_CONCAT(kt.slug ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_slugs,
102 GROUP_CONCAT(kt.title ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_titles
103 FROM (
104 SELECT
105 u.id,
106 u.mail,
107 u.forename,
108 u.surname,
109 u.pseudonym,
110 u.slug,
111 GREATEST(u.created, c.created, g.created) AS created,
112 GREATEST(u.updated, c.updated, g.updated) AS updated,
113 c.title AS civility,
114 GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS g_ids,
115 GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS g_titles
116 FROM RapsysBlogBundle:User AS u
117 JOIN RapsysBlogBundle:UserGroup AS gu ON (gu.user_id = u.id)
118 JOIN RapsysBlogBundle:Group AS g ON (g.id = gu.group_id)
119 JOIN RapsysBlogBundle:Civility AS c ON (c.id = u.civility_id)
120 GROUP BY u.id
121 ORDER BY NULL
122 LIMIT 0, :limit
123 ) AS c
124 LEFT JOIN RapsysBlogBundle:Article AS a ON (a.user_id = c.id)
125 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
126 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
127 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
128 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak.keyword_id AND at.locale = :locale)
129 GROUP BY a.id
130 ORDER BY NULL
131 LIMIT 0, :limit
132 ) AS t
133 GROUP BY t.id
134 ORDER BY t.id ASC
135 LIMIT :offset, :count
136 SQL;
137
138 //Replace bundle entity name by table name
139 $req = $this->replace($req);
140
141 //Get result set mapping instance
142 //XXX: DEBUG: see ../blog.orig/src/Rapsys/UserBundle/Repository/ArticleRepository.php
143 $rsm = new ResultSetMapping();
144
145 //Declare all fields
146 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
147 //addScalarResult($sqlColName, $resColName, $type = 'string');
148 $rsm->addScalarResult('id', 'id', 'integer')
149 ->addScalarResult('mail', 'mail', 'string')
150 ->addScalarResult('forename', 'forename', 'string')
151 ->addScalarResult('surname', 'surname', 'string')
152 ->addScalarResult('pseudonym', 'pseudonym', 'string')
153 ->addScalarResult('slug', 'slug', 'string')
154 ->addScalarResult('created', 'created', 'datetime')
155 ->addScalarResult('updated', 'updated', 'datetime')
156 ->addScalarResult('modified', 'modified', 'datetime')
157 ->addScalarResult('civility', 'civility', 'string')
158 //XXX: is a string because of \n separator
159 ->addScalarResult('g_ids', 'g_ids', 'string')
160 //XXX: is a string because of \n separator
161 ->addScalarResult('g_titles', 'g_titles', 'string')
162 //XXX: is a string because of \n separator
163 ->addScalarResult('a_ids', 'a_ids', 'string')
164 //XXX: is a string because of \n separator
165 ->addScalarResult('at_descriptions', 'at_descriptions', 'string')
166 //XXX: is a string because of \n separator
167 ->addScalarResult('at_slugs', 'at_slugs', 'string')
168 //XXX: is a string because of \n separator
169 ->addScalarResult('at_titles', 'at_titles', 'string')
170 //XXX: is a string because of \n separator
171 ->addScalarResult('ak_ids', 'ak_ids', 'string')
172 //XXX: is a string because of \n separator
173 ->addScalarResult('kt_slugs', 'kt_slugs', 'string')
174 //XXX: is a string because of \n separator
175 ->addScalarResult('kt_titles', 'kt_titles', 'string');
176
177 //Fetch result
178 $res = $this->_em
179 ->createNativeQuery($req, $rsm)
180 ->setParameter('offset', $page * $count)
181 ->setParameter('count', $count)
182 ->getResult();
183
184 //Init return
185 $ret = [];
186
187 //Process result
188 foreach($res as $data) {
189 //Set data
190 $ret[$data['id']] = [
191 'mail' => $data['mail'],
192 'forename' => $data['forename'],
193 'surname' => $data['surname'],
194 'pseudonym' => $data['pseudonym'],
195 'created' => $data['created'],
196 'updated' => $data['updated'],
197 'modified' => $data['modified'],
198 'civility' => $data['civility'],
199 #'slug' => $data['slug'],
200 'link' => $this->router->generate('rapsys_blog_user_view', ['id' => $data['id'], 'slug' => $data['slug']]),
201 'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)]),
202 'articles' => [],
203 'groups' => []
204 ];
205
206 //With groups
207 if (!empty($data['g_ids'])) {
208 //Set titles
209 $titles = explode("\n", $data['g_titles']);
210
211 //Iterate on each group
212 foreach(explode("\n", $data['g_ids']) as $k => $id) {
213 //Add group
214 $ret[$data['id']]['groups'][$id] = [
215 'title' => /*$group = */$this->translator->trans($titles[$k]),
216 #'slug' => $this->slugger->slug($group)
217 #'link' => $this->router->generate('rapsys_user_group_view', ['id' => $id, 'slug' => $this->slugger->short($group)])
218 ];
219 }
220 }
221
222 //With articles
223 if (!empty($data['a_ids'])) {
224 //Set descriptions
225 $descriptions = explode("\n", $data['at_descriptions']);
226
227 //Set slugs
228 $slugs = explode("\n", $data['at_slugs']);
229
230 //Set titles
231 $titles = explode("\n", $data['at_titles']);
232
233 //Set keyword ids
234 $keywords = [
235 'ids' => explode("\n", $data['ak_ids']),
236 'slugs' => explode("\n", $data['kt_slugs']),
237 'titles' => explode("\n", $data['kt_titles'])
238 ];
239
240 //Iterate on each dance
241 foreach(explode("\n", $data['a_ids']) as $k => $id) {
242 //Init article when missing
243 if (!isset($ret[$data['id']]['articles'][$id])) {
244 //Add article
245 $ret[$data['id']]['articles'][$id] = [
246 'description' => $descriptions[$k],
247 #'slug' => $slugs[$k],
248 'title' => $titles[$k],
249 'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slugs[$k]]),
250 //TODO: replace with keywords !!!
251 'keywords' => []
252 ];
253
254 //With article keywords
255 if (!empty($keywords['ids'][$k])) {
256 //Set slugs
257 $slugs = explode("\r", $keywords['slugs'][$k]);
258
259 //Set titles
260 $titles = explode("\r", $keywords['titles'][$k]);
261
262 //Iterate on each keyword
263 foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
264 //Add keyword
265 $ret[$data['id']]['articles'][$id]['keywords'][$kid] = [
266 #'slug' => $slugs[$k],
267 'title' => $titles[$k],
268 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slugs[$k]]),
269 ];
270 }
271 }
272 }
273 }
274 }
275 }
276
277 //Send result
278 return $ret;
279 }
280
281 /**
282 * Find user by id as array
283 *
284 * @param integer $id The user id
285 * @return array The article array
286 */
287 public function findByIdAsArray(int $id): array {
288 //Set the request
289 $req = <<<SQL
290 SELECT
291 t.id,
292 t.mail,
293 t.forename,
294 t.surname,
295 t.pseudonym,
296 t.slug,
297 t.created,
298 t.updated,
299 GREATEST(t.created, t.updated) AS modified,
300 t.civility,
301 t.g_ids,
302 t.g_titles,
303 GROUP_CONCAT(t.a_id ORDER BY t.a_id SEPARATOR "\\n") AS a_ids,
304 GROUP_CONCAT(t.at_description ORDER BY t.a_id SEPARATOR "\\n") AS at_descriptions,
305 GROUP_CONCAT(t.at_slug ORDER BY t.a_id SEPARATOR "\\n") AS at_slugs,
306 GROUP_CONCAT(t.at_title ORDER BY t.a_id SEPARATOR "\\n") AS at_titles,
307 GROUP_CONCAT(t.ak_ids ORDER BY t.a_id SEPARATOR "\\n") AS ak_ids,
308 GROUP_CONCAT(t.kt_slugs ORDER BY t.a_id SEPARATOR "\\n") AS kt_slugs,
309 GROUP_CONCAT(t.kt_titles ORDER BY t.a_id SEPARATOR "\\n") AS kt_titles
310 FROM (
311 SELECT
312 c.id,
313 c.mail,
314 c.forename,
315 c.surname,
316 c.pseudonym,
317 c.slug,
318 GREATEST(c.created, COALESCE(a.created, 0), COALESCE(at.created, 0), COALESCE(k.created, 0), COALESCE(kt.created, 0)) AS created,
319 GREATEST(c.updated, COALESCE(a.updated, 0), COALESCE(at.updated, 0), COALESCE(k.updated, 0), COALESCE(kt.updated, 0)) AS updated,
320 c.civility,
321 c.g_ids,
322 c.g_titles,
323 a.id AS a_id,
324 at.description AS at_description,
325 at.slug AS at_slug,
326 at.title AS at_title,
327 GROUP_CONCAT(ak.keyword_id ORDER BY ak.keyword_id SEPARATOR "\\r") AS ak_ids,
328 GROUP_CONCAT(kt.slug ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_slugs,
329 GROUP_CONCAT(kt.title ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_titles
330 FROM (
331 SELECT
332 u.id,
333 u.mail,
334 u.forename,
335 u.surname,
336 u.pseudonym,
337 u.slug,
338 GREATEST(u.created, c.created, g.created) AS created,
339 GREATEST(u.updated, c.updated, g.updated) AS updated,
340 c.title AS civility,
341 GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS g_ids,
342 GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS g_titles
343 FROM RapsysBlogBundle:User AS u
344 JOIN RapsysBlogBundle:UserGroup AS gu ON (gu.user_id = u.id)
345 JOIN RapsysBlogBundle:Group AS g ON (g.id = gu.group_id)
346 JOIN RapsysBlogBundle:Civility AS c ON (c.id = u.civility_id)
347 WHERE u.id = :id
348 GROUP BY u.id
349 ORDER BY NULL
350 LIMIT 0, :limit
351 ) AS c
352 LEFT JOIN RapsysBlogBundle:Article AS a ON (a.user_id = c.id)
353 LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
354 LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
355 LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
356 LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak.keyword_id AND at.locale = :locale)
357 GROUP BY a.id
358 ORDER BY NULL
359 LIMIT 0, :limit
360 ) AS t
361 GROUP BY t.id
362 SQL;
363
364 //Replace bundle entity name by table name
365 $req = $this->replace($req);
366
367 //Get result set mapping instance
368 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
369 $rsm = new ResultSetMapping();
370
371 //Declare all fields
372 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
373 //addScalarResult($sqlColName, $resColName, $type = 'string');
374 $rsm->addScalarResult('id', 'id', 'integer')
375 ->addScalarResult('mail', 'mail', 'string')
376 ->addScalarResult('forename', 'forename', 'string')
377 ->addScalarResult('surname', 'surname', 'string')
378 ->addScalarResult('pseudonym', 'pseudonym', 'string')
379 ->addScalarResult('slug', 'slug', 'string')
380 ->addScalarResult('created', 'created', 'datetime')
381 ->addScalarResult('updated', 'updated', 'datetime')
382 ->addScalarResult('modified', 'modified', 'datetime')
383 ->addScalarResult('civility', 'civility', 'string')
384 //XXX: is a string because of \n separator
385 ->addScalarResult('g_ids', 'g_ids', 'string')
386 //XXX: is a string because of \n separator
387 ->addScalarResult('g_titles', 'g_titles', 'string')
388 //XXX: is a string because of \n separator
389 ->addScalarResult('a_ids', 'a_ids', 'string')
390 //XXX: is a string because of \n separator
391 ->addScalarResult('at_descriptions', 'at_descriptions', 'string')
392 //XXX: is a string because of \n separator
393 ->addScalarResult('at_slugs', 'at_slugs', 'string')
394 //XXX: is a string because of \n separator
395 ->addScalarResult('at_titles', 'at_titles', 'string')
396 //XXX: is a string because of \n separator
397 ->addScalarResult('ak_ids', 'ak_ids', 'string')
398 //XXX: is a string because of \n separator
399 ->addScalarResult('kt_slugs', 'kt_slugs', 'string')
400 //XXX: is a string because of \n separator
401 ->addScalarResult('kt_titles', 'kt_titles', 'string');
402
403 //Get data
404 $data = $this->_em
405 ->createNativeQuery($req, $rsm)
406 ->setParameter('id', $id)
407 ->getOneOrNullResult();
408
409 //Set return
410 $return = [
411 'id' => $data['id'],
412 'mail' => $data['mail'],
413 'forename' => $data['forename'],
414 'surname' => $data['surname'],
415 'pseudonym' => $data['pseudonym'],
416 'created' => $data['created'],
417 'updated' => $data['updated'],
418 'modified' => $data['modified'],
419 'civility' => $data['civility'],
420 'slug' => $data['slug'],
421 'link' => $this->router->generate('rapsys_blog_user_view', ['id' => $data['id'], 'slug' => $data['slug']]),
422 'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)]),
423 'articles' => [],
424 'groups' => []
425 ];
426
427 //With groups
428 if (!empty($data['g_ids'])) {
429 //Set titles
430 $titles = explode("\n", $data['g_titles']);
431
432 //Iterate on each group
433 foreach(explode("\n", $data['g_ids']) as $k => $id) {
434 //Add group
435 $return['groups'][$id] = [
436 'title' => /*$group = */$this->translator->trans($titles[$k]),
437 #'slug' => $this->slugger->slug($group)
438 #'link' => $this->router->generate('rapsys_user_group_view', ['id' => $id, 'slug' => $this->slugger->short($group)])
439 ];
440 }
441 }
442
443 //With articles
444 if (!empty($data['a_ids'])) {
445 //Set descriptions
446 $descriptions = explode("\n", $data['at_descriptions']);
447
448 //Set slugs
449 $slugs = explode("\n", $data['at_slugs']);
450
451 //Set titles
452 $titles = explode("\n", $data['at_titles']);
453
454 //Set keyword ids
455 $keywords = [
456 'ids' => explode("\n", $data['ak_ids']),
457 'slugs' => explode("\n", $data['kt_slugs']),
458 'titles' => explode("\n", $data['kt_titles'])
459 ];
460
461 //Iterate on each dance
462 foreach(explode("\n", $data['a_ids']) as $k => $id) {
463 //Init article when missing
464 if (!isset($ret[$data['id']]['articles'][$id])) {
465 //Add article
466 $return['articles'][$id] = [
467 'description' => $descriptions[$k],
468 #'slug' => $slugs[$k],
469 'title' => $titles[$k],
470 'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slugs[$k]]),
471 //TODO: replace with keywords !!!
472 'keywords' => []
473 ];
474
475 //With article keywords
476 if (!empty($keywords['ids'][$k])) {
477 //Set slugs
478 $slugs = explode("\r", $keywords['slugs'][$k]);
479
480 //Set titles
481 $titles = explode("\r", $keywords['titles'][$k]);
482
483 //Iterate on each keyword
484 foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
485 //Add keyword
486 $return['articles'][$id]['keywords'][$kid] = [
487 #'slug' => $slugs[$k],
488 'title' => $titles[$k],
489 'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slugs[$k]]),
490 ];
491 }
492 }
493 }
494 }
495 }
496
497 //Return return
498 return $return;
499 }
500
501 /**
502 * {@inheritdoc}
503 */
504 public function upgradePassword(PasswordAuthenticatedUserInterface $user, string $hash): void {
505 //Set new hashed password
506 $user->setPassword($hash);
507
508 //Flush data to database
509 $this->getEntityManager()->flush();
510 }
511 }