Simple latest comment list

[anoweco.git] / src / anoweco / Storage.php

diff --git a/src/anoweco/Storage.php b/src/anoweco/Storage.php
index 4508f1017713da520a90903453c64285ae6f24d0..6a7e204ac2ef0d6a59e92741f7c988de5103bcd2 100644 (file)
--- a/src/anoweco/Storage.php
+++ b/src/anoweco/Storage.php
@@ -92,6 +92,49 @@ class Storage
         return $json;
     }
 
+    /**
+     * @return null|object NULL if not found, JSON comment object otherwise
+     *                     - "Xrow" property contains the database row object
+     *                     - "user" property contains the user db row object
+     */
+    public function listLatest()
+    {
+        $stmt = $this->db->prepare(
+            'SELECT comment_id, comment_user_id, comment_published'
+            . ', comment_of_url, comment_type'
+            . ' FROM comments'
+            . ' ORDER BY comment_published DESC'
+            . ' LIMIT 20'
+        );
+        $stmt->execute();
+        $rows = $stmt->fetchAll(\PDO::FETCH_OBJ);
+        if (!count($rows)) {
+            return [];
+        }
+        $userIds = array_values(
+            array_unique(array_column($rows, 'comment_user_id'))
+        );
+
+
+        $placeholders = implode(',', array_fill(0, count($userIds), '?'));
+        $stmt = $this->db->prepare(
+            'SELECT * FROM users WHERE user_id IN (' . $placeholders . ')'
+        );
+        $stmt->execute($userIds);
+
+        $users = $stmt->fetchAll(\PDO::FETCH_OBJ);
+        $users = array_combine(
+            array_column($users, 'user_id'),
+            $users
+        );
+
+        foreach ($rows as $row) {
+            $row->user = $users[$row->comment_user_id];
+        }
+
+        return $rows;
+    }
+
     /**
      * @return null|object NULL if not found, user database row otherwise
      */