1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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
}
|
