aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2016-03-09 01:13:00 -0500
committerPaul Duncan <pabs@pablotron.org>2016-03-09 01:13:00 -0500
commitd73612df9875b57d9df1f64f757146e4accf7a30 (patch)
treec564c0d23af2cb81c3009f01c57138317613e1c2
parent94529553ac724a11b6821dd1374c98b0f51b4f14 (diff)
downloadold-guff-d73612df9875b57d9df1f64f757146e4accf7a30.tar.bz2
old-guff-d73612df9875b57d9df1f64f757146e4accf7a30.zip
add get_tags
-rw-r--r--src/guff/models/tag.cr70
1 files changed, 66 insertions, 4 deletions
diff --git a/src/guff/models/tag.cr b/src/guff/models/tag.cr
index 997cdfd..c828ebe 100644
--- a/src/guff/models/tag.cr
+++ b/src/guff/models/tag.cr
@@ -1,11 +1,38 @@
module Guff
class TagModel < Model
SQL = TemplateCache.new({
+ get_tag_posts: "
+ SELECT a.post_id
+
+ FROM post_tags a
+ JOIN tags b
+ ON (b.tag_id = a.tag_id)
+
+ WHERE b.name = '%{tag}'
+ ",
+
+ get_tags: "
+ SELECT a.tag_id,
+ a.name,
+ COUNT(*) AS num_posts
+
+ FROM tags a
+ JOIN post_tags b
+ ON (b.tag_id = a.tag_id)
+ JOIN (SELECT DISTINCT post_id FROM (%{tags})) c
+ ON (c.post_id = b.post_id)
+
+ WHERE %{filter}
+
+ GROUP BY a.tag_id, a.name
+ ORDER BY num_posts, a.name DESC
+ ",
+
add_tags: "
INSERT INTO tags(name) VALUES %{tags}
",
- get_tags: "
+ get_ids: "
SELECT tag_id,
name
@@ -19,7 +46,42 @@ module Guff
super(models, SQL)
end
- def add_tags(tags : Array(String))
+ def get_tags(
+ tags = [] of String : Array(String),
+ show_all = false : Bool,
+ ) : Array(Hash(String, String | Int32))
+ r = [] of Hash(String, String | Int32)
+
+ # build filter
+ if show_all
+ filter = "1 = 1"
+ else
+ filter = "a.name NOT LIKE '\\_%'"
+ end
+
+ # exec query
+ all(:get_tags, nil, {
+ "filter": filter,
+ "tags": tags.map { |tag|
+ template(:get_tag_posts, {
+ "tag": @db.quote(tag),
+ })
+ }.join(" INTERSECT "),
+ }) do |row|
+ r << {
+ "tag_id": row["tag_id"].to_s.to_i,
+ "name": row["name"]?.to_s,
+ "num_posts": row["num_posts"].to_s.to_i,
+ }
+ end
+
+ # return results
+ r
+ end
+
+ def add_tags(
+ tags = [] of String : Array(String)
+ )
missing_tags = get_missing_tags(tags)
if missing_tags.size > 0
@@ -32,7 +94,7 @@ module Guff
end
private def get_missing_tags(
- tags : Array(String)
+ tags = [] of String : Array(String)
) : Array(String)
# get ids of existing tags
ids = get_ids(tags)
@@ -46,7 +108,7 @@ module Guff
) : Hash(String, Int32)
r = {} of String => Int32
- all(:get_tags, nil, {
+ all(:get_ids, nil, {
"tags": tags.map { |tag|
"'" + @db.quote(tag) + "'"
}.join(','),