1 files changed, 63 insertions, 0 deletions
diff --git a/src/server/database/posts.go b/src/server/database/posts.go
new file mode 100644
index 0000000..033f5ae
--- /dev/null
+++ b/src/server/database/posts.go
@@ -0,0 +1,63 @@
+package database
+
+import (
+	"database/sql"
+)
+
+type Post struct {
+	Uid         int    `json:"uid"`
+	Author      string `json:"author"`
+	TimeUpdated int    `json:"time_updated"`
+	Subreact    string `json:"subreact"`
+	Title       string `json:"title"`
+	Contents    string `json:"contents"`
+}
+
+func GetPosts(subreact string, page int, amount int) ([]Post, error) {
+	var db = GetDb()
+
+	// This gets posts without parents (threads) and its latest updated time,
+	// which could be the post itself.
+    const query = "SELECT Parent.uid, Parent.author, COALESCE(Latest.updated_time, Parent.time) AS bump_time, Parent.subreact, " +
+        "Parent.title, Parent.contents " +
+        "FROM Posts as Parent " +
+        "    LEFT OUTER JOIN ( " +
+        "        SELECT Posts.parent, Posts.uid, MAX(Posts.time) AS updated_time " +
+        "        FROM Posts " +
+        "        WHERE Posts.parent IS NOT NULL " +
+        "        ORDER BY Posts.time " +
+        "    ) AS LATEST " +
+        "    ON Latest.parent = Parent.uid " +
+        "WHERE Parent.subreact = (CASE WHEN (? = \"\") then Parent.subreact ELSE ? END) " +
+        "ORDER BY bump_time DESC " +
+        "LIMIT ? " +
+        "OFFSET ?;"
+
+	rows, err := db.Query(query, subreact, subreact, amount, page*amount)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	posts := make([]Post, 0)
+	for rows.Next() {
+		var post Post
+		if err = rows.Scan(
+			&post.Uid,
+			&post.Author,
+			&post.TimeUpdated,
+			&post.Subreact,
+			&post.Title,
+			&post.Contents); err != nil {
+
+			if err == sql.ErrNoRows {
+				return posts, nil
+			}
+
+			return nil, err
+		}
+		posts = append(posts, post)
+	}
+
+	return posts, nil
+}