add get_tags

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(','),