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
;
15 use Symfony\Component\Routing\Generator\UrlGeneratorInterface
;
16 use Symfony\Component\Security\Core\User\PasswordAuthenticatedUserInterface
;
17 use Symfony\Component\Security\Core\User\PasswordUpgraderInterface
;
22 class UserRepository
extends EntityRepository
implements PasswordUpgraderInterface
{
24 * Find user count as int
26 * @return integer The keywords count
28 public function findCountAsInt(): int {
31 SELECT COUNT(u.id) AS count
32 FROM RapsysBlogBundle:User AS u
35 //Get result set mapping instance
36 $req = $this->replace($req);
38 //Get result set mapping instance
39 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
40 $rsm = new ResultSetMapping();
43 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
44 //addScalarResult($sqlColName, $resColName, $type = 'string');
45 $rsm->addScalarResult('count', 'count', 'integer');
49 ->createNativeQuery($req, $rsm)
50 ->getSingleScalarResult();
54 * Find all users as array
56 * @param integer $page The page
57 * @param integer $count The count
58 * @return array The users sorted by id
60 public function findAllAsArray(int $page, int $count): array {
72 GREATEST(t.created, t.updated) AS modified,
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
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,
97 at.description AS at_description,
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
111 GREATEST(u.created, c.created, g.created) AS created,
112 GREATEST(u.updated, c.updated, g.updated) AS updated,
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)
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)
135 LIMIT :offset, :count
138 //Replace bundle entity name by table name
139 $req = $this->replace($req);
141 //Get result set mapping instance
142 //XXX: DEBUG: see ../blog.orig/src/Rapsys/UserBundle/Repository/ArticleRepository.php
143 $rsm = new ResultSetMapping();
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');
179 ->createNativeQuery($req, $rsm)
180 ->setParameter('offset', $page * $count)
181 ->setParameter('count', $count)
188 foreach($res as $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)]),
207 if (!empty($data['g_ids'])) {
209 $titles = explode("\n", $data['g_titles']);
211 //Iterate on each group
212 foreach(explode("\n", $data['g_ids']) as $k => $id) {
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)])
223 if (!empty($data['a_ids'])) {
225 $descriptions = explode("\n", $data['at_descriptions']);
228 $slugs = explode("\n", $data['at_slugs']);
231 $titles = explode("\n", $data['at_titles']);
235 'ids' => explode("\n", $data['ak_ids']),
236 'slugs' => explode("\n", $data['kt_slugs']),
237 'titles' => explode("\n", $data['kt_titles'])
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])) {
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 !!!
254 //With article keywords
255 if (!empty($keywords['ids'][$k])) {
257 $slugs = explode("\r", $keywords['slugs'][$k]);
260 $titles = explode("\r", $keywords['titles'][$k]);
262 //Iterate on each keyword
263 foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
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]]),
282 * Find user by id as array
284 * @param integer $id The user id
285 * @return array The article array
287 public function findByIdAsArray(int $id): array {
299 GREATEST(t.created, t.updated) AS modified,
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
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,
324 at.description AS at_description,
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
338 GREATEST(u.created, c.created, g.created) AS created,
339 GREATEST(u.updated, c.updated, g.updated) AS updated,
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)
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)
364 //Replace bundle entity name by table name
365 $req = $this->replace($req);
367 //Get result set mapping instance
368 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
369 $rsm = new ResultSetMapping();
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');
405 ->createNativeQuery($req, $rsm)
406 ->setParameter('id', $id)
407 ->getOneOrNullResult();
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)]),
428 if (!empty($data['g_ids'])) {
430 $titles = explode("\n", $data['g_titles']);
432 //Iterate on each group
433 foreach(explode("\n", $data['g_ids']) as $k => $id) {
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)])
444 if (!empty($data['a_ids'])) {
446 $descriptions = explode("\n", $data['at_descriptions']);
449 $slugs = explode("\n", $data['at_slugs']);
452 $titles = explode("\n", $data['at_titles']);
456 'ids' => explode("\n", $data['ak_ids']),
457 'slugs' => explode("\n", $data['kt_slugs']),
458 'titles' => explode("\n", $data['kt_titles'])
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])) {
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 !!!
475 //With article keywords
476 if (!empty($keywords['ids'][$k])) {
478 $slugs = explode("\r", $keywords['slugs'][$k]);
481 $titles = explode("\r", $keywords['titles'][$k]);
483 //Iterate on each keyword
484 foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
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]]),
504 public function upgradePassword(PasswordAuthenticatedUserInterface
$user, string $hash): void {
505 //Set new hashed password
506 $user->setPassword($hash);
508 //Flush data to database
509 $this->getEntityManager()->flush();