add created_at sorting and use json for sort fields

6 files changed, 97 insertions, 35 deletions

diff --git a/src/guff/api/methods.cr b/src/guff/api/methods.cr
index 6c38eb3..9b35596 100644
--- a/src/guff/api/methods.cr
+++ b/src/guff/api/methods.cr
@@ -15,20 +15,38 @@ module Guff
                 required: false,
               },
 
-              "year": {
-                text:     "Year filter",
+              "posted_year": {
+                text:     "Post date year filter",
                 type:     :int,
                 required: false,
               },
 
-              "month": {
-                text:     "Month filter",
+              "posted_month": {
+                text:     "Post date month filter",
                 type:     :int,
                 required: false,
               },
 
-              "day": {
-                text:     "Day filter",
+              "posted_day": {
+                text:     "Post date day filter",
+                type:     :int,
+                required: false,
+              },
+
+              "created_year": {
+                text:     "Creation date year filter",
+                type:     :int,
+                required: false,
+              },
+
+              "created_month": {
+                text:     "Creation date month filter",
+                type:     :int,
+                required: false,
+              },
+
+              "created_day": {
+                text:     "Creation date day filter",
                 type:     :int,
                 required: false,
               },
@@ -48,7 +66,7 @@ module Guff
 
               "sort": {
                 text:     "Sort order of results",
-                type:     :sort,
+                type:     :json,
                 required: false,
               },
 
@@ -340,7 +358,6 @@ module Guff
         slug:     /^[a-z0-9\.-]+$/,
         int:      /^\d+$/,
         int_list: /^\d+(?:,\d+)*$/,
-        sort:     /^[a-z0-9_]+,(?:asc|desc)$/,
         state:    /^(?:draft|posted|deleted)$/,
 
         # FIXME: lock these down more
diff --git a/src/guff/api/post.cr b/src/guff/api/post.cr
index 499e7c0..f856d5b 100644
--- a/src/guff/api/post.cr
+++ b/src/guff/api/post.cr
@@ -4,12 +4,15 @@ module Guff
   module API
     module PostAPI
       GET_POSTS_FILTERS = {
-        q:      "q",
-        year:   "year",
-        month:  "month",
-        day:    "day",
-        slug:   "slug",
-        state:  "state",
+        q:              "q",
+        posted_year:    "posted_year",
+        posted_month:   "posted_month",
+        posted_day:     "posted_day",
+        created_year:   "created_year",
+        created_month:  "created_month",
+        created_day:    "created_day",
+        slug:           "slug",
+        state:          "state",
       }
 
       private def do_post_get_posts(
@@ -26,8 +29,9 @@ module Guff
         @models.post.get_posts(
           site_id:  get_site(context),
           filters:  filters,
-          tags:     get_posts_tags(args["tags"]),
+          tags:     get_posts_tags(args["tags"]?),
           page:     args.has_key?("page") ? args["page"].to_i : 1,
+          sort:     get_posts_sort(args["sort"]?),
         )
       end
 
diff --git a/src/guff/api/util.cr b/src/guff/api/util.cr
index 525f098..72e7e5c 100644
--- a/src/guff/api/util.cr
+++ b/src/guff/api/util.cr
@@ -26,6 +26,14 @@ module Guff
           [] of Array(String)
         end
       end
+
+      private def get_posts_sort(
+        s : String?
+      ) : Array(Hash(String, String))?
+        if s && s.size > 0
+          Array(Hash(String, String)).from_json(s)
+        end
+      end
     end
   end
 end
diff --git a/src/guff/handlers/blog.cr b/src/guff/handlers/blog.cr
index b961955..e9d9763 100644
--- a/src/guff/handlers/blog.cr
+++ b/src/guff/handlers/blog.cr
@@ -166,14 +166,16 @@ class Guff::Handlers::BlogHandler < Guff::Handler
   #######################
 
   FILTERS = {
-    year:   "year",
-    month:  "month",
-    day:    "day",
-    slug:   "slug",
+    posted_year:  "year",
+    posted_month: "month",
+    posted_day:   "day",
+    slug:         "slug",
   }
 
   private def get_post_filters(md) : Hash(Symbol, String)
-    FILTERS.reduce({} of Symbol => String) do |r, k, s|
+    FILTERS.reduce({
+      state: "posted"
+    }) do |r, k, s|
       r[k] = md[s] if md[s]?
       r
     end
diff --git a/src/guff/migrations.cr b/src/guff/migrations.cr
index 4763929..0e50fdb 100644
--- a/src/guff/migrations.cr
+++ b/src/guff/migrations.cr
@@ -98,6 +98,8 @@ module Guff
       CREATE INDEX in_posts_slug ON posts(slug)
     }, %{
       CREATE INDEX in_posts_state ON posts(state)
+    }, %{
+      CREATE INDEX in_posts_posted_at ON posts(posted_at)
     }],
   }, {
     id: "4-create-tags",
diff --git a/src/guff/models/post.cr b/src/guff/models/post.cr
index 7814d9f..826bb7c 100644
--- a/src/guff/models/post.cr
+++ b/src/guff/models/post.cr
@@ -39,7 +39,7 @@ module Guff
          WHERE a.site_id = :site_id
            AND %{filter}
 
-         ORDER BY %{sort} %{dir}
+         ORDER BY %{sort}
          LIMIT :limit OFFSET :offset
       ",
 
@@ -58,7 +58,7 @@ module Guff
       remove_posts: "
         UPDATE posts
 
-           SET state = (SELECT state 
+           SET state = (SELECT state
                           FROM post_states
                          WHERE name = 'deleted')
 
@@ -162,8 +162,7 @@ module Guff
       tags = [] of Array(String)        : Array(Array(String)),
       page = 1                          : Int,
       limit = 20                        : Int,
-      sort = "posted_at"                : String,
-      dir = "desc"                      : String,
+      sort = nil                        : Array(Hash(String, String))?,
     )
       # build sql args
       sql_args = {
@@ -174,7 +173,6 @@ module Guff
       tmpl_args = {
         "filter":   get_filter_clause(filters),
         "sort":     get_sort_clause(sort),
-        "dir":      dir,
         "tags":     get_tags_filter(tags),
         "cols":     get_columns_clause(cols),
       }
@@ -204,24 +202,42 @@ module Guff
     end
 
     FILTERS = {
-      year: {
+      posted_year: {
         type: :int,
         re:   /^\d{4}$/,
         sql:  "(strftime('%%Y', a.posted_at) + 0 = %d)",
       },
 
-      month: {
+      posted_month: {
         type: :int,
         re:   /^\d{1,2}$/,
         sql:  "(strftime('%%m', a.posted_at) + 0 = %d)",
       },
 
-      day: {
+      posted_day: {
         type: :int,
         re:   /^\d{1,2}$/,
         sql:  "(strftime('%%d', a.posted_at) + 0 = %d)",
       },
 
+      created_year: {
+        type: :int,
+        re:   /^\d{4}$/,
+        sql:  "(strftime('%%Y', a.created_at) + 0 = %d)",
+      },
+
+      created_month: {
+        type: :int,
+        re:   /^\d{1,2}$/,
+        sql:  "(strftime('%%m', a.created_at) + 0 = %d)",
+      },
+
+      created_day: {
+        type: :int,
+        re:   /^\d{1,2}$/,
+        sql:  "(strftime('%%d', a.created_at) + 0 = %d)",
+      },
+
       slug: {
         type: :string,
         sql:  "a.slug = '%s'"
@@ -273,15 +289,28 @@ module Guff
       r.join(" AND ")
     end
 
+    GET_POSTS_DEFAULT_SORT = [{
+      "col": "posted_at",
+      "dir": "desc",
+    }, {
+      "col": "created_at",
+      "dir": "desc",
+    }]
+
     private def get_sort_clause(
-      sort = "posted_at" : String
+      sort = nil : Array(Hash(String, String))?
     ) : String
-      # verify sort column
-      raise "unknown sort column" unless COLUMNS.has_key?(sort)
-      raise "column is not sortable" unless COLUMNS[sort][:sortable]
-
-      # return result
-      COLUMNS[sort][:clause] as String
+      (sort || GET_POSTS_DEFAULT_SORT).map { |row|
+        # verify sort column
+        raise "unknown sort column" unless COLUMNS.has_key?(row["col"])
+        raise "column is not sortable" unless COLUMNS[row["col"]][:sortable]
+
+        # build result
+        "%s %s" % [
+          COLUMNS[row["col"]][:clause] as String,
+          (row["dir"]? == "desc") ? "desc" : "asc",
+        ]
+      }.join(',')
     end
 
     private def get_columns_clause(