add created_at sorting and use json for sort fields

1 files changed, 44 insertions, 15 deletions

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(