From 9f8c1fce2c94c9fb040586740e28b3e83a3cc41f Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Wed, 9 Mar 2016 15:49:25 -0500 Subject: add created_at sorting and use json for sort fields --- src/guff/api/methods.cr | 33 +++++++++++++++++++------- src/guff/api/post.cr | 18 +++++++++------ src/guff/api/util.cr | 8 +++++++ src/guff/handlers/blog.cr | 12 ++++++---- src/guff/migrations.cr | 2 ++ src/guff/models/post.cr | 59 +++++++++++++++++++++++++++++++++++------------ 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( -- cgit v1.2.3