From b20cf03c18ad06a5d9df4fa7bc61734fc57ba41b Mon Sep 17 00:00:00 2001
From: Arno Richter <oelna@oelna.de>
Date: Fri, 13 Jan 2023 14:32:18 +0100
Subject: more efficient attachment retrival from DB for timeline rendering.

---
 lib/functions.php          | 47 ++++++++++++++++++++++++++++++++++++----------
 templates/single.inc.php   |  9 ++++-----
 templates/timeline.inc.php |  8 ++++----
 3 files changed, 45 insertions(+), 19 deletions(-)

diff --git a/lib/functions.php b/lib/functions.php
index 97c93fe..3f69b56 100644
--- a/lib/functions.php
+++ b/lib/functions.php
@@ -98,9 +98,14 @@ function db_select_post($id=0) {
 	return (!empty($row)) ? $row : false;
 }
 
-function db_get_attached_files($post_id, $include_deleted=false) {
+function db_get_attached_files($post_ids=[], $include_deleted=false) {
 	global $db;
 	if(empty($db)) return false;
+	if(empty($post_ids)) return [];
+	if(!is_array($post_ids)) {
+		// accomodate shorthand syntax with single ID
+		$post_ids = [$post_ids];
+	}
 
 	$rows = [];
 
@@ -110,19 +115,27 @@ function db_get_attached_files($post_id, $include_deleted=false) {
 		$sql = 'SELECT f.* FROM files f LEFT JOIN file_to_post p WHERE f.id = p.file_id AND p.post_id = :post_id AND p.deleted IS NULL ORDER BY f.file_timestamp ASC';
 	}
 
+	$statement = $db->prepare($sql);
+
+	$result = [];
 	try {
-		$statement = $db->prepare($sql);
-		$statement->bindValue(':post_id', $post_id, PDO::PARAM_INT);
 		
-		$statement->execute();
+		foreach($post_ids as $id) {
+			$statement->bindParam(':post_id', $id, PDO::PARAM_INT);
+			$statement->execute();
 
-		$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
+			$result[$id] = [];
+
+			while ($row = $statement->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
+				$result[$id][] = $row;
+			}
+		}
 	} catch(PDOException $e) {
 		// print 'Exception : '.$e->getMessage();
 		return false;
 	}
 
-	return (!empty($rows)) ? $rows : false;
+	return (!empty($result)) ? $result : false;
 }
 
 function db_select_posts($from, $amount=10, $sort='desc', $offset=0) {
@@ -426,9 +439,13 @@ function rebuild_json_feed($posts=[]) {
 		'items' => array()
 	);
 
+	$post_ids = array_column($posts, 'id');
+	$attached_files = db_get_attached_files($post_ids);
+
 	foreach($posts as $post) {
 
-		$attachments = db_get_attached_files($post['id']);
+		// $attachments = db_get_attached_files($post['id']);
+		$attachments = !empty($attached_files[$post['id']]) ? $attached_files[$post['id']] : [];
 		$post_attachments = [];
 		if(!empty($attachments)) {
 			foreach ($attachments as $a) {
@@ -453,8 +470,6 @@ function rebuild_json_feed($posts=[]) {
 			'image' => !empty($post_images) ? $post_images[0]['url'] : '',
 			'attachments' => $post_attachments
 		);
-
-		
 	}
 
 	if(file_put_contents($filename, json_encode($feed, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES))) {
@@ -478,8 +493,13 @@ function rebuild_atom_feed($posts=[]) {
 	$feed .= '<id>'.$config['url'].'</id>'.NL;
 	$feed .= '<updated>'.gmdate('Y-m-d\TH:i:s\Z').'</updated>'.NL;
 
+	$post_ids = array_column($posts, 'id');
+	$attached_files = db_get_attached_files($post_ids);
+
 	foreach($posts as $post) {
 
+		$post_images = !empty($attached_files[$post['id']]) ? $attached_files[$post['id']] : [];
+
 		$published = gmdate('Y-m-d\TH:i:s\Z', $post['post_timestamp']);
 		$updated = ($post['post_edited'] > $post['post_timestamp']) ? gmdate('Y-m-d\TH:i:s\Z', $post['post_edited']) : $published;
 
@@ -489,7 +509,14 @@ function rebuild_atom_feed($posts=[]) {
 		$feed .= '<id>'.($post['post_guid'] ? 'urn:uuid:'.$post['post_guid'] : $config['url'].'/'.$post['id']).'</id>'.NL;
 		$feed .= '<updated>'.$updated.'</updated>'.NL;
 		$feed .= '<published>'.$published.'</published>'.NL;
-		$feed .= '<content type="text">'.$post['post_content'].'</content>'.NL;
+		
+		if(!empty($post_images)) {
+			// todo: render attached images
+			$feed .= '<content type="text">'.$post['post_content'].'</content>'.NL;
+		} else {
+			$feed .= '<content type="text">'.$post['post_content'].'</content>'.NL;
+		}
+
 		$feed .= '</entry>'.NL;
 	}
 
diff --git a/templates/single.inc.php b/templates/single.inc.php
index 64e7f6d..5514742 100644
--- a/templates/single.inc.php
+++ b/templates/single.inc.php
@@ -109,8 +109,8 @@
 							<?php
 								$attachments = db_get_attached_files($post['id']);
 							?>
-							<?php if(!empty($attachments)): ?>
-								<?php foreach($attachments as $a): ?>
+							<?php if(!empty($attachments) && !empty($attachments[$post['id']])): ?>
+								<?php foreach($attachments[$post['id']] as $a): ?>
 									<?php if(strpos($a['file_mime_type'], 'image') === 0): ?>
 									<?php
 										$abs = ROOT.DS.get_file_path($a);
@@ -142,7 +142,6 @@
 					$formatted_time = date_format($date, 'M d Y H:i');
 
 					$attachments = db_get_attached_files($post['id']);
-					// var_dump($attachments);
 				?>
 				<span class="post-timestamp">
 					<time class="published" datetime="<?= $datetime ?>" data-unix-time="<?= $post['post_timestamp'] ?>"><?= $formatted_time ?></time>
@@ -161,9 +160,9 @@
 					</ul><?php endif; ?>
 				</nav>
 				<div class="post-content"><?= nl2br(autolink($post['post_content'])) ?></div>
-				<?php if(!empty($attachments)): ?>
+				<?php if(!empty($attachments) && !empty($attachments[$post['id']])): ?>
 				<ul class="post-attachments">
-					<?php foreach($attachments as $a): ?>
+					<?php foreach($attachments[$post['id']] as $a): ?>
 					<li>
 						<?php if(strpos($a['file_mime_type'], 'image') === 0): ?>
 							<?php
diff --git a/templates/timeline.inc.php b/templates/timeline.inc.php
index 6dd37c1..d07776d 100644
--- a/templates/timeline.inc.php
+++ b/templates/timeline.inc.php
@@ -52,14 +52,14 @@
 					</ul><?php endif; ?>
 				</nav>
 				<div class="post-content"><?= nl2br(autolink($post['post_content'])) ?></div>
-				<?php if(!empty($attachments)): ?>
+				<?php if(!empty($attachments) && !empty($attachments[$post['id']])): ?>
 				<?php
-					$attachments_total = count($attachments);
+					$attachments_total = count($attachments[$post['id']]);
 					// only display the first attachment on the timeline
-					array_splice($attachments, 1);
+					array_splice($attachments[$post['id']], 1);
 				?>
 				<ul class="post-attachments">
-					<?php foreach($attachments as $a): ?>
+					<?php foreach($attachments[$post['id']] as $a): ?>
 					<li title="<?= ($attachments_total > 1) ? 'and '.($attachments_total-1).' more' : '' ?>">
 						<?php if(strpos($a['file_mime_type'], 'image') === 0): ?>
 							<?php
-- 
cgit v1.2.3