From: Raphaël Gertz <git@rapsys.eu>
Date: Tue, 12 Dec 2023 17:39:04 +0000 (+0100)
Subject: Add findByIdAsArray function
X-Git-Tag: 0.2~7
X-Git-Url: https://git.rapsys.eu/blogbundle/commitdiff_plain/2aca0f531ee6833b663648cc5205da37743ed2d0

Add findByIdAsArray function
Add created, updated and modified to findAllAsArray
---

diff --git a/Repository/UserRepository.php b/Repository/UserRepository.php
index 6963751..befe695 100644
--- a/Repository/UserRepository.php
+++ b/Repository/UserRepository.php
@@ -67,6 +67,9 @@ SELECT
 	t.surname,
 	t.pseudonym,
 	t.slug,
+	t.created,
+	t.updated,
+	GREATEST(t.created, t.updated) AS modified,
 	t.civility,
 	t.g_ids,
 	t.g_titles,
@@ -85,6 +88,8 @@ FROM (
 		c.surname,
 		c.pseudonym,
 		c.slug,
+		GREATEST(c.created, COALESCE(a.created, 0), COALESCE(at.created, 0), COALESCE(k.created, 0), COALESCE(kt.created, 0)) AS created,
+		GREATEST(c.updated, COALESCE(a.updated, 0), COALESCE(at.updated, 0), COALESCE(k.updated, 0), COALESCE(kt.updated, 0)) AS updated,
 		c.civility,
 		c.g_ids,
 		c.g_titles,
@@ -103,6 +108,8 @@ FROM (
 			u.surname,
 			u.pseudonym,
 			u.slug,
+			GREATEST(u.created, c.created, g.created) AS created,
+			GREATEST(u.updated, c.updated, g.updated) AS updated,
 			c.title AS civility,
 			GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS g_ids,
 			GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS g_titles
@@ -110,12 +117,14 @@ FROM (
 		JOIN RapsysBlogBundle:UserGroup AS gu ON (gu.user_id = u.id)
 		JOIN RapsysBlogBundle:Group AS g ON (g.id = gu.group_id)
 		JOIN RapsysBlogBundle:Civility AS c ON (c.id = u.civility_id)
+		GROUP BY u.id
 		ORDER BY NULL
 		LIMIT 0, :limit
 	) AS c
 	LEFT JOIN RapsysBlogBundle:Article AS a ON (a.user_id = c.id)
 	LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
 	LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
+	LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
 	LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak.keyword_id AND at.locale = :locale)
 	GROUP BY a.id
 	ORDER BY NULL
@@ -142,6 +151,9 @@ SQL;
 			->addScalarResult('surname', 'surname', 'string')
 			->addScalarResult('pseudonym', 'pseudonym', 'string')
 			->addScalarResult('slug', 'slug', 'string')
+			->addScalarResult('created', 'created', 'datetime')
+			->addScalarResult('updated', 'updated', 'datetime')
+			->addScalarResult('modified', 'modified', 'datetime')
 			->addScalarResult('civility', 'civility', 'string')
 			//XXX: is a string because of \n separator
 			->addScalarResult('g_ids', 'g_ids', 'string')
@@ -180,6 +192,10 @@ SQL;
 				'forename' => $data['forename'],
 				'surname' => $data['surname'],
 				'pseudonym' => $data['pseudonym'],
+				'created' => $data['created'],
+				'updated' => $data['updated'],
+				'modified' => $data['modified'],
+				'civility' => $data['civility'],
 				#'slug' => $data['slug'],
 				'link' => $this->router->generate('rapsys_blog_user_view', ['id' => $data['id'], 'slug' => $data['slug']]),
 				'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)]),
@@ -262,6 +278,226 @@ SQL;
 		return $ret;
 	}
 
+	/**
+	 * Find user by id as array
+	 *
+	 * @param integer $id The user id
+	 * @return array The article array
+	 */
+	public function findByIdAsArray(int $id): array {
+		//Set the request
+		$req = <<<SQL
+SELECT
+	t.id,
+	t.mail,
+	t.forename,
+	t.surname,
+	t.pseudonym,
+	t.slug,
+	t.created,
+	t.updated,
+	GREATEST(t.created, t.updated) AS modified,
+	t.civility,
+	t.g_ids,
+	t.g_titles,
+	GROUP_CONCAT(t.a_id ORDER BY t.a_id SEPARATOR "\\n") AS a_ids,
+	GROUP_CONCAT(t.at_description ORDER BY t.a_id SEPARATOR "\\n") AS at_descriptions,
+	GROUP_CONCAT(t.at_slug ORDER BY t.a_id SEPARATOR "\\n") AS at_slugs,
+	GROUP_CONCAT(t.at_title ORDER BY t.a_id SEPARATOR "\\n") AS at_titles,
+	GROUP_CONCAT(t.ak_ids ORDER BY t.a_id SEPARATOR "\\n") AS ak_ids,
+	GROUP_CONCAT(t.kt_slugs ORDER BY t.a_id SEPARATOR "\\n") AS kt_slugs,
+	GROUP_CONCAT(t.kt_titles ORDER BY t.a_id SEPARATOR "\\n") AS kt_titles
+FROM (
+	SELECT
+		c.id,
+		c.mail,
+		c.forename,
+		c.surname,
+		c.pseudonym,
+		c.slug,
+		GREATEST(c.created, COALESCE(a.created, 0), COALESCE(at.created, 0), COALESCE(k.created, 0), COALESCE(kt.created, 0)) AS created,
+		GREATEST(c.updated, COALESCE(a.updated, 0), COALESCE(at.updated, 0), COALESCE(k.updated, 0), COALESCE(kt.updated, 0)) AS updated,
+		c.civility,
+		c.g_ids,
+		c.g_titles,
+		a.id AS a_id,
+		at.description AS at_description,
+		at.slug AS at_slug,
+		at.title AS at_title,
+		GROUP_CONCAT(ak.keyword_id ORDER BY ak.keyword_id SEPARATOR "\\r") AS ak_ids,
+		GROUP_CONCAT(kt.slug ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_slugs,
+		GROUP_CONCAT(kt.title ORDER BY ak.keyword_id SEPARATOR "\\r") AS kt_titles
+	FROM (
+		SELECT
+			u.id,
+			u.mail,
+			u.forename,
+			u.surname,
+			u.pseudonym,
+			u.slug,
+			GREATEST(u.created, c.created, g.created) AS created,
+			GREATEST(u.updated, c.updated, g.updated) AS updated,
+			c.title AS civility,
+			GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS g_ids,
+			GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS g_titles
+		FROM RapsysBlogBundle:User AS u
+		JOIN RapsysBlogBundle:UserGroup AS gu ON (gu.user_id = u.id)
+		JOIN RapsysBlogBundle:Group AS g ON (g.id = gu.group_id)
+		JOIN RapsysBlogBundle:Civility AS c ON (c.id = u.civility_id)
+		WHERE u.id = :id
+		GROUP BY u.id
+		ORDER BY NULL
+		LIMIT 0, :limit
+	) AS c
+	LEFT JOIN RapsysBlogBundle:Article AS a ON (a.user_id = c.id)
+	LEFT JOIN RapsysBlogBundle:ArticleTranslation AS at ON (at.article_id = a.id AND at.locale = :locale)
+	LEFT JOIN RapsysBlogBundle:ArticleKeyword AS ak ON (ak.article_id = a.id)
+	LEFT JOIN RapsysBlogBundle:Keyword AS k ON (k.id = ak.keyword_id)
+	LEFT JOIN RapsysBlogBundle:KeywordTranslation AS kt ON (kt.keyword_id = ak.keyword_id AND at.locale = :locale)
+	GROUP BY a.id
+	ORDER BY NULL
+	LIMIT 0, :limit
+) AS t
+GROUP BY t.id
+SQL;
+
+		//Replace bundle entity name by table name
+		$req = $this->replace($req);
+
+		//Get result set mapping instance
+		//XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+		$rsm = new ResultSetMapping();
+
+		//Declare all fields
+		//XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+		//addScalarResult($sqlColName, $resColName, $type = 'string');
+		$rsm->addScalarResult('id', 'id', 'integer')
+			->addScalarResult('mail', 'mail', 'string')
+			->addScalarResult('forename', 'forename', 'string')
+			->addScalarResult('surname', 'surname', 'string')
+			->addScalarResult('pseudonym', 'pseudonym', 'string')
+			->addScalarResult('slug', 'slug', 'string')
+			->addScalarResult('created', 'created', 'datetime')
+			->addScalarResult('updated', 'updated', 'datetime')
+			->addScalarResult('modified', 'modified', 'datetime')
+			->addScalarResult('civility', 'civility', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('g_ids', 'g_ids', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('g_titles', 'g_titles', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('a_ids', 'a_ids', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('at_descriptions', 'at_descriptions', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('at_slugs', 'at_slugs', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('at_titles', 'at_titles', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('ak_ids', 'ak_ids', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('kt_slugs', 'kt_slugs', 'string')
+			//XXX: is a string because of \n separator
+			->addScalarResult('kt_titles', 'kt_titles', 'string');
+
+		//Get data
+		$data = $this->_em
+			->createNativeQuery($req, $rsm)
+			->setParameter('id', $id)
+			->getOneOrNullResult();
+
+		//Set return
+		$return = [
+			'id' => $data['id'],
+			'mail' => $data['mail'],
+			'forename' => $data['forename'],
+			'surname' => $data['surname'],
+			'pseudonym' => $data['pseudonym'],
+			'created' => $data['created'],
+			'updated' => $data['updated'],
+			'modified' => $data['modified'],
+			'civility' => $data['civility'],
+			'slug' => $data['slug'],
+			'link' => $this->router->generate('rapsys_blog_user_view', ['id' => $data['id'], 'slug' => $data['slug']]),
+			'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)]),
+			'articles' => [],
+			'groups' => []
+		];
+
+		//With groups
+		if (!empty($data['g_ids'])) {
+			//Set titles
+			$titles = explode("\n", $data['g_titles']);
+
+			//Iterate on each group
+			foreach(explode("\n", $data['g_ids']) as $k => $id) {
+				//Add group
+				$return['groups'][$id] = [
+					'title' => /*$group = */$this->translator->trans($titles[$k]),
+					#'slug' => $this->slugger->slug($group)
+					#'link' => $this->router->generate('rapsys_user_group_view', ['id' => $id, 'slug' => $this->slugger->short($group)])
+				];
+			}
+		}
+
+		//With articles
+		if (!empty($data['a_ids'])) {
+			//Set descriptions
+			$descriptions = explode("\n", $data['at_descriptions']);
+
+			//Set slugs
+			$slugs = explode("\n", $data['at_slugs']);
+
+			//Set titles
+			$titles = explode("\n", $data['at_titles']);
+
+			//Set keyword ids
+			$keywords = [
+				'ids' => explode("\n", $data['ak_ids']),
+				'slugs' => explode("\n", $data['kt_slugs']),
+				'titles' => explode("\n", $data['kt_titles'])
+			];
+
+			//Iterate on each dance
+			foreach(explode("\n", $data['a_ids']) as $k => $id) {
+				//Init article when missing
+				if (!isset($ret[$data['id']]['articles'][$id])) {
+					//Add article
+					$return['articles'][$id] = [
+						'description' => $descriptions[$k],
+						#'slug' => $slugs[$k],
+						'title' => $titles[$k],
+						'link' => $this->router->generate('rapsys_blog_article_view', ['id' => $id, 'slug' => $slugs[$k]]),
+						//TODO: replace with keywords !!!
+						'keywords' => []
+					];
+
+					//With article keywords
+					if (!empty($keywords['ids'][$k])) {
+						//Set slugs
+						$slugs = explode("\r", $keywords['slugs'][$k]);
+
+						//Set titles
+						$titles = explode("\r", $keywords['titles'][$k]);
+
+						//Iterate on each keyword
+						foreach(explode("\r", $keywords['ids'][$k]) as $k => $kid) {
+							//Add keyword
+							$return['articles'][$id]['keywords'][$kid] = [
+								#'slug' => $slugs[$k],
+								'title' => $titles[$k],
+								'link' => $this->router->generate('rapsys_blog_keyword_view', ['id' => $kid, 'slug' => $slugs[$k]]),
+							];
+						}
+					}
+				}
+			}
+		}
+
+		//Return return
+		return $return;
+	}
+
 	/**
 	 * {@inheritdoc}
 	 */