From 36ab1673930235f0883a8bd824df24634a799a2a Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Mon, 23 May 2016 23:41:39 -0400 Subject: add get_posts --- src/guff.cr | 154 ++++++++++++++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 129 insertions(+), 25 deletions(-) (limited to 'src') 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 -- cgit v1.2.3