chaos!

1 files changed, 114 insertions, 17 deletions

diff --git a/src/guff/post-model.cr b/src/guff/post-model.cr
index 112589b..4e2bf63 100644
--- a/src/guff/post-model.cr
+++ b/src/guff/post-model.cr
@@ -1,35 +1,132 @@
 module Guff
   class PostModel < Model
-    SQL = {
+    SQL = TemplateCache.new({
+      tags: "
+        SELECT DISTINCT
+               post_id
+
+          FROM (%{sql})
+      ",
+
+      tag: "
+        SELECT a.post_id
+
+          FROM post_tags a
+          JOIN tags b
+            ON (b.tag_id = a.tag_id)
+
+         WHERE b.name = '%{tag}'
+      ",
+
+      count_posts: "
+        SELECT COUNT(*)
+
+          FROM posts a
+          JOIN (%{tags}) b
+            ON (b.post_id = a.post_id)
+
+         WHERE a.site_id = :site_id
+           AND %{filter}
+      ",
+
       get_posts: "
         SELECT a.post_id,
+               a.site_id,
                a.name,
                a.body,
-               a.html
+               a.html,
 
-          FROM posts a
-          JOIN (
-            SELECT DISTICT
-                   c.post_id
+               (SELECT group_agg(d.name, '|')
 
-              FROM post_tags c
+                  FROM post_tags c
+                  JOIN tags d
+                    ON (d.tag_id = c.tag_id)
 
-              JOIN tags d
-                ON (d.tag_id = c.tag_id)
-              JOIN (VALUES (%{tags}) e(name)
-                ON (e.name = d.name)
-          ) b
+                 WHERE d.post_id = a.post_id) AS tags
 
-         WHERE c.%{filter}
+          FROM posts a
+          JOIN (%{tags}) b
+            ON (b.post_id = a.post_id)
+
+         WHERE a.site_id = :site_id
+           AND %{filter}
 
          ORDER BY %{sort} %{dir}
-         OFFSET ? LIMIT ?
+         OFFSET :offset LIMIT :limit
       ",
+    })
+
+    def initialize(models : Models)
+      super(models, SQL)
+    end
+
+    SORTS = {
+      "posted_at": "a.posted_at",
     }
 
-    def get_posts(req)
-      # TODO
-      [{foo: "bar"}, {foo: "asdf"}]
+    def get_posts(
+      site_id = nil               : Int32?,
+      q = ""                      : String,
+      tags = [] of Array(String)  : Array(Array(String)),
+      page = 1                    : Int32,
+      limit = 20                  : Int32,
+      sort = "posted_at"          : String,
+      dir = "desc"                : String,
+    )
+      # build sql args
+      sql_args = {
+        "site_id": "0" # (site_id || @models.default_site_id).to_s,
+      } of String => String
+
+      # build tmpl args
+      tmpl_args = {
+        # TODO
+        "filter":   "1 = 1", # true
+        "sort":     SORTS[sort],
+        "dir":      dir,
+        "tags":     get_tags_filter(tags),
+      } of String => String
+
+      # count number of matching rows
+      num_rows = (one(:count_posts, sql_args, tmpl_args).to_s || 0).to_i
+
+      # TODO: add Post class?
+      rows = [] of Hash(String, ::SQLite3::Value)
+      if num_rows > 0
+        all(:get_posts, sql_args.merge({
+          ":offset":  ((page - 1) * limit).to_s,
+          ":limit":   limit.to_s,
+        }), tmpl_args) do |row|
+          rows << row
+          nil
+        end
+      end
+
+      # return result
+      Results(Hash(String, ::SQLite3::Value)).new(
+        page:     page,
+        limit:    limit,
+        num_rows: num_rows,
+        rows:     rows,
+      )
+    end
+
+    private def get_tags_filter(tag_sets : Array(Array(String)))
+      (tag_sets.size > 0) ? template(:tags, {
+        "sql": tag_sets.map { |tags|
+          template(:tags, {
+            "sql": tags.map { |tag|
+              template(:tag, {
+                "tag": @models.db.quote(tag),
+              })
+            }.join(" INTERSECTS "),
+          })
+        }.join(" UNION "),
+      }) : "
+        SELECT post_id
+          FROM posts
+         LIMIT 0
+      "
     end
 
     def add_post(req)