add get_posts

1 files changed, 129 insertions, 25 deletions

diff --git a/src/guff.cr b/src/guff.cr
index 01e0cb4..3f36363 100644
--- a/src/guff.cr
+++ b/src/guff.cr
@@ -234,34 +234,55 @@ module Guff
            WHERE post_id = ?
         ",
 
-        unused: "
-            post_id     INTEGER PRIMARY KEY,
+        count_posts: "
+          SELECT COUNT(*)
 
-            site_id     INTEGER NOT NULL
-                        REFERENCES sites(site_id),
+            FROM posts a
 
-            created_at  TIMESTAMP WITH TIME ZONE NOT NULL
-                        DEFAULT CURRENT_TIMESTAMP,
+            JOIN states b
+              ON (b.state_id = a.state_id)
+            LEFT JOIN blogs c
+              ON (c.post_id = a.post_id)
+            LEFT JOIN pages d
+              ON (d.post_id = a.post_id)
+            LEFT JOIN projects e
+              ON (e.post_id = a.post_id)
 
-            created_by  INTEGER NOT NULL
-                        REFERENCES users(user_id),
-
-            state_id    INTEGER NOT NULL
-                        REFERENCES states(state_id),
-
-            posted_at   TIMESTAMP WITH TIME ZONE,
-            expires_at  TIMESTAMP WITH TIME ZONE,
-
-            name        TEXT NOT NULL DEFAULT '',
-
-            slug        TEXT NOT NULL DEFAULT '' CHECK (
-                          slug NOT LIKE '% %' AND
-                          slug = LOWER(slug)
-                        ),
-
-            slug_lock   BOOLEAN NOT NULL DEFAULT true,
+           WHERE %s
+        ",
 
-            body        TEXT NOT NULL DEFAULT ''
+        get_posts: "
+          SELECT a.post_id,
+                 a.site_id,
+                 b.state,
+                 a.posted_at,
+                 a.expires_at,
+                 a.slug,
+                 a.name,
+
+                 (CASE WHEN c.post_id IS NOT NULL THEN
+                   'blog'
+                 WHEN d.post_id IS NOT NULL THEN
+                   'page'
+                 WHEN e.post_id IS NOT NULL THEN
+                   'project'
+                 END) as post_type
+
+            FROM posts a
+            JOIN states b
+              ON (b.state_id = a.state_id)
+            LEFT JOIN blogs c
+              ON (c.post_id = a.post_id)
+            LEFT JOIN pages d
+              ON (d.post_id = a.post_id)
+            LEFT JOIN projects e
+              ON (e.post_id = a.post_id)
+
+           WHERE %s
+
+           ORDER BY COALESCE(a.posted_at, a.created_at) DESC
+
+           LIMIT ? OFFSET ?
         ",
       }
 
@@ -352,6 +373,84 @@ module Guff
           @context.dbs.rw.query(SQL[:set] % sets.join(","), args)
         end
       end
+
+      LIMIT = 50
+
+      def get_posts(
+        site_id   : Int64?   = nil,
+        post_type : String?  = nil,
+        state     : String?  = nil,
+        page      : Int32    = 1,
+      )
+        filters = %w{1}
+        args = [] of String
+
+        # check page
+        raise "invalid page: #{page}" unless page > 0
+
+        if site_id
+          filters << "a.site_id = ?"
+          args << site_id.to_s
+        end
+
+        if post_type
+          filters << case post_type
+          when "blog"
+            "c.post_id IS NOT NULL"
+          when "page"
+            "d.post_id IS NOT NULL"
+          when "project"
+            "e.post_id IS NOT NULL"
+          else
+            raise "unknown post type: #{post_type}"
+          end
+        end
+
+        if state
+          filters << "b.state = ?"
+          args << state
+        else
+          filters << "b.state IN ('draft', 'posted')"
+        end
+
+        # build where clause
+        filter_sql = filters.join(" AND ")
+
+        # count matching rows
+        num_posts = (@context.dbs.ro.one(SQL[:count_posts] % [
+          filter_sql
+        ], args) || "0").to_i64
+
+        # build result
+        rows = [] of Hash(String, String)
+
+        if num_posts > 0
+          # get matching rows
+          @context.dbs.ro.all(SQL[:get_posts] % [
+            filter_sql
+          ], args.concat([
+            LIMIT.to_s,
+            ((page - 1) * LIMIT).to_s,
+          ])) do |row|
+            # append row to result
+            rows << row.reduce({} of String => String) do |r, k, v|
+              r[k] = v.to_s
+              r
+            end
+          end
+        end
+
+        # return results
+        {
+          "meta": {
+            "page":      page,
+            "num_posts": num_posts,
+            "num_pages": (1.0 * num_posts / LIMIT).ceil
+          },
+
+          "rows": rows,
+        }
+      end
     end
 
     class PageModel < Model
@@ -1138,7 +1237,12 @@ module Guff
   module APIs
     module PostAPI
       def do_post_get_posts(params : HTTP::Params)
-        { "asdf": "foo" }
+        @context.models.post.get_posts(
+          site_id:    params["site_id"]? ? params["site_id"].to_i64 : nil,
+          state:      params["state"]?,
+          post_type:  params["post_type"]?,
+          page:       params["page"].to_i32,
+        )
       end
     end