- commit
- 7c00b4a
- parent
- f61835e
- author
- Eric Bower
- date
- 2022-08-25 15:44:50 +0000 UTC
feat(prose,lists): filter posts by all tags read Now users can filter posts by tag on the read page. This will allow us to host writing competitions where users can submit their article with the tag specifically for the competition and then we read them to pick a winner.
12 files changed,
+163,
-48
M
db/db.go
+2,
-2
1@@ -150,8 +150,8 @@ type DB interface {
2
3 ReplaceTagsForPost(tags []string, postID string) error
4 FindUserPostsByTag(tag, userID, space string) ([]*Post, error)
5- FindPostsByTag(tag, space string) ([]*Post, error)
6- FindPopularTags() ([]string, error)
7+ FindPostsByTag(pager *Pager, tag, space string) (*Paginate[*Post], error)
8+ FindPopularTags(space string) ([]string, error)
9 FindTagsForPost(postID string) ([]string, error)
10
11 AddViewCount(postID string) (int, error)
+53,
-28
1@@ -84,16 +84,28 @@ var (
2 cur_space = $2
3 ORDER BY publish_at DESC`, SelectPost)
4
5- sqlSelectPostsByTag = fmt.Sprintf(`
6- SELECT %s
7+ sqlSelectPostsByTag = `
8+ SELECT
9+ posts.id,
10+ user_id,
11+ filename,
12+ slug,
13+ title,
14+ text,
15+ description,
16+ publish_at,
17+ app_users.name as username,
18+ posts.updated_at,
19+ 0 AS "score"
20 FROM posts
21 LEFT OUTER JOIN app_users ON app_users.id = posts.user_id
22 LEFT OUTER JOIN post_tags ON post_tags.post_id = posts.id
23 WHERE
24- post_tags.name = '$1' AND
25+ post_tags.name = $3 AND
26 publish_at::date <= CURRENT_DATE AND
27- cur_space = $2
28- ORDER BY publish_at DESC`, SelectPost)
29+ cur_space = $4
30+ ORDER BY publish_at DESC
31+ LIMIT $1 OFFSET $2`
32
33 sqlSelectUserPostsByTag = fmt.Sprintf(`
34 SELECT %s
35@@ -137,6 +149,11 @@ const (
36 sqlSelectFeatureForUser = `SELECT id FROM feature_flags WHERE user_id = $1 AND name = $2`
37 sqlSelectSizeForUser = `SELECT sum(file_size) FROM posts WHERE user_id = $1`
38
39+ sqlSelectTagPostCount = `
40+ SELECT count(posts.id)
41+ FROM posts
42+ LEFT OUTER JOIN post_tags ON post_tags.post_id = posts.id
43+ WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`
44 sqlSelectPostCount = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
45 sqlSelectAllUpdatedPosts = `
46 SELECT
47@@ -184,7 +201,14 @@ const (
48 ORDER BY score DESC
49 LIMIT $1 OFFSET $2`
50
51- sqlSelectPopularTags = `SELECT name, count(post_id) as tally FROM post_tags GROUP_BY name, post_id ORDER BY tally DESC LIMIT 10`
52+ sqlSelectPopularTags = `
53+ SELECT name, count(post_id) as "tally"
54+ FROM post_tags
55+ LEFT OUTER JOIN posts ON posts.id = post_id
56+ WHERE posts.cur_space = $1
57+ GROUP BY name
58+ ORDER BY tally DESC
59+ LIMIT 5`
60 sqlSelectTagsForPost = `SELECT name FROM post_tags WHERE post_id=$1`
61
62 sqlInsertPublicKey = `INSERT INTO public_keys (user_id, public_key) VALUES ($1, $2)`
63@@ -567,7 +591,7 @@ func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
64 return post, nil
65 }
66
67-func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string) (*db.Paginate[*db.Post], error) {
68+func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
69 var posts []*db.Post
70 for rs.Next() {
71 post := &db.Post{}
72@@ -595,7 +619,12 @@ func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string) (*db.Pagina
73 }
74
75 var count int
76- err := me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
77+ var err error
78+ if tag == "" {
79+ err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
80+ } else {
81+ err = me.Db.QueryRow(sqlSelectTagPostCount, space, tag).Scan(&count)
82+ }
83 if err != nil {
84 return nil, err
85 }
86@@ -613,7 +642,7 @@ func (me *PsqlDB) FindAllPosts(page *db.Pager, space string) (*db.Paginate[*db.P
87 if err != nil {
88 return nil, err
89 }
90- return me.postPager(rs, page.Num, space)
91+ return me.postPager(rs, page.Num, space, "")
92 }
93
94 func (me *PsqlDB) FindAllUpdatedPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
95@@ -621,7 +650,7 @@ func (me *PsqlDB) FindAllUpdatedPosts(page *db.Pager, space string) (*db.Paginat
96 if err != nil {
97 return nil, err
98 }
99- return me.postPager(rs, page.Num, space)
100+ return me.postPager(rs, page.Num, space, "")
101 }
102
103 func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
104@@ -858,35 +887,31 @@ func (me *PsqlDB) FindUserPostsByTag(tag, userID, space string) ([]*db.Post, err
105 return posts, nil
106 }
107
108-func (me *PsqlDB) FindPostsByTag(tag, space string) ([]*db.Post, error) {
109- var posts []*db.Post
110- rs, err := me.Db.Query(sqlSelectPostsByTag, tag, space)
111+func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
112+ rs, err := me.Db.Query(
113+ sqlSelectPostsByTag,
114+ pager.Num,
115+ pager.Num*pager.Page,
116+ tag,
117+ space,
118+ )
119 if err != nil {
120- return posts, err
121+ return nil, err
122 }
123- for rs.Next() {
124- post, err := CreatePostFromRow(rs)
125- if err != nil {
126- return nil, err
127- }
128
129- posts = append(posts, post)
130- }
131- if rs.Err() != nil {
132- return posts, rs.Err()
133- }
134- return posts, nil
135+ return me.postPager(rs, pager.Num, space, tag)
136 }
137
138-func (me *PsqlDB) FindPopularTags() ([]string, error) {
139+func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
140 tags := make([]string, 0)
141- rs, err := me.Db.Query(sqlSelectPopularTags)
142+ rs, err := me.Db.Query(sqlSelectPopularTags, space)
143 if err != nil {
144 return tags, err
145 }
146 for rs.Next() {
147 name := ""
148- err := rs.Scan(&name)
149+ tally := 0
150+ err := rs.Scan(&name, &tally)
151 if err != nil {
152 return tags, err
153 }
+4,
-0
1@@ -289,6 +289,10 @@ figure {
2 margin: 0;
3 }
4
5+.mb {
6+ margin-bottom: 0.5rem;
7+}
8+
9 .my {
10 margin-top: 0.5rem;
11 margin-bottom: 0.5rem;
+31,
-8
1@@ -44,10 +44,12 @@ type BlogPageData struct {
2 }
3
4 type ReadPageData struct {
5- Site shared.SitePageData
6- NextPage string
7- PrevPage string
8- Posts []PostItemData
9+ Site shared.SitePageData
10+ NextPage string
11+ PrevPage string
12+ Posts []PostItemData
13+ Tags []string
14+ HasFilter bool
15 }
16
17 type PostPageData struct {
18@@ -407,7 +409,15 @@ func readHandler(w http.ResponseWriter, r *http.Request) {
19 cfg := shared.GetCfg(r)
20
21 page, _ := strconv.Atoi(r.URL.Query().Get("page"))
22- pager, err := dbpool.FindAllUpdatedPosts(&db.Pager{Num: 30, Page: page}, cfg.Space)
23+ tag := r.URL.Query().Get("tag")
24+ var pager *db.Paginate[*db.Post]
25+ var err error
26+ if tag == "" {
27+ pager, err = dbpool.FindAllUpdatedPosts(&db.Pager{Num: 30, Page: page}, cfg.Space)
28+ } else {
29+ pager, err = dbpool.FindPostsByTag(&db.Pager{Num: 30, Page: page}, tag, cfg.Space)
30+ }
31+
32 if err != nil {
33 logger.Error(err)
34 http.Error(w, err.Error(), http.StatusInternalServerError)
35@@ -425,17 +435,30 @@ func readHandler(w http.ResponseWriter, r *http.Request) {
36 nextPage := ""
37 if page < pager.Total-1 {
38 nextPage = fmt.Sprintf("/read?page=%d", page+1)
39+ if tag != "" {
40+ nextPage = fmt.Sprintf("%s&tag=%s", nextPage, tag)
41+ }
42 }
43
44 prevPage := ""
45 if page > 0 {
46 prevPage = fmt.Sprintf("/read?page=%d", page-1)
47+ if tag != "" {
48+ prevPage = fmt.Sprintf("%s&tag=%s", prevPage, tag)
49+ }
50+ }
51+
52+ tags, err := dbpool.FindPopularTags(cfg.Space)
53+ if err != nil {
54+ logger.Error(err)
55 }
56
57 data := ReadPageData{
58- Site: *cfg.GetSiteData(),
59- NextPage: nextPage,
60- PrevPage: prevPage,
61+ Site: *cfg.GetSiteData(),
62+ NextPage: nextPage,
63+ PrevPage: prevPage,
64+ Tags: tags,
65+ HasFilter: tag != "",
66 }
67 for _, post := range pager.Data {
68 item := PostItemData{
+12,
-1
1@@ -13,10 +13,21 @@
2 <hr />
3 </header>
4 <main>
5- <div class="my">
6+ <div class="flex items-center">
7+ <div class="font-italic text-sm post-date">popular tags</div>
8+ <div class="flex-1">
9+ {{range .Tags}}
10+ <a href="/read?tag={{.}}">#{{.}}</a>
11+ {{end}}
12+ </div>
13+ </div>
14+ {{if .HasFilter}}<a href="/read">clear filter</a>{{end}}
15+
16+ <div class="mb">
17 {{if .PrevPage}}<a href="{{.PrevPage}}">prev</a>{{else}}<span class="text-grey">prev</span>{{end}}
18 {{if .NextPage}}<a href="{{.NextPage}}">next</a>{{else}}<span class="text-grey">next</span>{{end}}
19 </div>
20+
21 {{range .Posts}}
22 <article class="my">
23 <div class="flex items-center">
+4,
-0
1@@ -289,6 +289,10 @@ figure {
2 margin: 0;
3 }
4
5+.mb {
6+ margin-bottom: 0.5rem;
7+}
8+
9 .my {
10 margin-top: 0.5rem;
11 margin-bottom: 0.5rem;
+4,
-0
1@@ -289,6 +289,10 @@ figure {
2 margin: 0;
3 }
4
5+.mb {
6+ margin-bottom: 0.5rem;
7+}
8+
9 .my {
10 margin-top: 0.5rem;
11 margin-bottom: 0.5rem;
+31,
-8
1@@ -52,10 +52,12 @@ type BlogPageData struct {
2 }
3
4 type ReadPageData struct {
5- Site shared.SitePageData
6- NextPage string
7- PrevPage string
8- Posts []PostItemData
9+ Site shared.SitePageData
10+ NextPage string
11+ PrevPage string
12+ Posts []PostItemData
13+ Tags []string
14+ HasFilter bool
15 }
16
17 type PostPageData struct {
18@@ -450,7 +452,15 @@ func readHandler(w http.ResponseWriter, r *http.Request) {
19 cfg := shared.GetCfg(r)
20
21 page, _ := strconv.Atoi(r.URL.Query().Get("page"))
22- pager, err := dbpool.FindAllPosts(&db.Pager{Num: 30, Page: page}, cfg.Space)
23+ tag := r.URL.Query().Get("tag")
24+ var pager *db.Paginate[*db.Post]
25+ var err error
26+ if tag == "" {
27+ pager, err = dbpool.FindAllPosts(&db.Pager{Num: 30, Page: page}, cfg.Space)
28+ } else {
29+ pager, err = dbpool.FindPostsByTag(&db.Pager{Num: 30, Page: page}, tag, cfg.Space)
30+ }
31+
32 if err != nil {
33 logger.Error(err)
34 http.Error(w, err.Error(), http.StatusInternalServerError)
35@@ -468,17 +478,30 @@ func readHandler(w http.ResponseWriter, r *http.Request) {
36 nextPage := ""
37 if page < pager.Total-1 {
38 nextPage = fmt.Sprintf("/read?page=%d", page+1)
39+ if tag != "" {
40+ nextPage = fmt.Sprintf("%s&tag=%s", nextPage, tag)
41+ }
42 }
43
44 prevPage := ""
45 if page > 0 {
46 prevPage = fmt.Sprintf("/read?page=%d", page-1)
47+ if tag != "" {
48+ prevPage = fmt.Sprintf("%s&tag=%s", prevPage, tag)
49+ }
50+ }
51+
52+ tags, err := dbpool.FindPopularTags(cfg.Space)
53+ if err != nil {
54+ logger.Error(err)
55 }
56
57 data := ReadPageData{
58- Site: *cfg.GetSiteData(),
59- NextPage: nextPage,
60- PrevPage: prevPage,
61+ Site: *cfg.GetSiteData(),
62+ NextPage: nextPage,
63+ PrevPage: prevPage,
64+ Tags: tags,
65+ HasFilter: tag != "",
66 }
67 for _, post := range pager.Data {
68 item := PostItemData{
+2,
-0
1@@ -138,6 +138,8 @@ Cya!
2 <h2 class="text-lg font-bold">Roadmap</h2>
3 <ol>
4 <li>Ability to upload images</li>
5+ <li>View posts by tag</li>
6+ <li>Writing competitions</li>
7 <li><a href="/help#hugo-support">Limited compatibility</a> with <a href="https://gohugo.io">hugo</a></li>
8 </ol>
9 </section>
+12,
-1
1@@ -16,10 +16,21 @@
2 <hr />
3 </header>
4 <main>
5- <div class="my">
6+ <div class="flex items-center">
7+ <div class="font-italic text-sm post-date">popular tags</div>
8+ <div class="flex-1">
9+ {{range .Tags}}
10+ <a href="/read?tag={{.}}">#{{.}}</a>
11+ {{end}}
12+ </div>
13+ </div>
14+ {{if .HasFilter}}<a href="/read">clear filter</a>{{end}}
15+
16+ <div class="mb">
17 {{if .PrevPage}}<a href="{{.PrevPage}}">prev</a>{{else}}<span class="text-grey">prev</span>{{end}}
18 {{if .NextPage}}<a href="{{.NextPage}}">next</a>{{else}}<span class="text-grey">next</span>{{end}}
19 </div>
20+
21 {{range .Posts}}
22 <article class="my">
23 <div class="flex items-center">
+4,
-0
1@@ -289,6 +289,10 @@ figure {
2 margin: 0;
3 }
4
5+.mb {
6+ margin-bottom: 0.5rem;
7+}
8+
9 .my {
10 margin-top: 0.5rem;
11 margin-bottom: 0.5rem;
M
smol.css
+4,
-0
1@@ -289,6 +289,10 @@ figure {
2 margin: 0;
3 }
4
5+.mb {
6+ margin-bottom: 0.5rem;
7+}
8+
9 .my {
10 margin-top: 0.5rem;
11 margin-bottom: 0.5rem;