diff options
Diffstat (limited to 'src/guff/post-model.cr')
-rw-r--r-- | src/guff/post-model.cr | 131 |
1 files changed, 114 insertions, 17 deletions
diff --git a/src/guff/post-model.cr b/src/guff/post-model.cr index 112589b..4e2bf63 100644 --- a/src/guff/post-model.cr +++ b/src/guff/post-model.cr @@ -1,35 +1,132 @@ module Guff class PostModel < Model - SQL = { + SQL = TemplateCache.new({ + tags: " + SELECT DISTINCT + post_id + + FROM (%{sql}) + ", + + tag: " + SELECT a.post_id + + FROM post_tags a + JOIN tags b + ON (b.tag_id = a.tag_id) + + WHERE b.name = '%{tag}' + ", + + count_posts: " + SELECT COUNT(*) + + FROM posts a + JOIN (%{tags}) b + ON (b.post_id = a.post_id) + + WHERE a.site_id = :site_id + AND %{filter} + ", + get_posts: " SELECT a.post_id, + a.site_id, a.name, a.body, - a.html + a.html, - FROM posts a - JOIN ( - SELECT DISTICT - c.post_id + (SELECT group_agg(d.name, '|') - FROM post_tags c + FROM post_tags c + JOIN tags d + ON (d.tag_id = c.tag_id) - JOIN tags d - ON (d.tag_id = c.tag_id) - JOIN (VALUES (%{tags}) e(name) - ON (e.name = d.name) - ) b + WHERE d.post_id = a.post_id) AS tags - WHERE c.%{filter} + FROM posts a + JOIN (%{tags}) b + ON (b.post_id = a.post_id) + + WHERE a.site_id = :site_id + AND %{filter} ORDER BY %{sort} %{dir} - OFFSET ? LIMIT ? + OFFSET :offset LIMIT :limit ", + }) + + def initialize(models : Models) + super(models, SQL) + end + + SORTS = { + "posted_at": "a.posted_at", } - def get_posts(req) - # TODO - [{foo: "bar"}, {foo: "asdf"}] + def get_posts( + site_id = nil : Int32?, + q = "" : String, + tags = [] of Array(String) : Array(Array(String)), + page = 1 : Int32, + limit = 20 : Int32, + sort = "posted_at" : String, + dir = "desc" : String, + ) + # build sql args + sql_args = { + "site_id": "0" # (site_id || @models.default_site_id).to_s, + } of String => String + + # build tmpl args + tmpl_args = { + # TODO + "filter": "1 = 1", # true + "sort": SORTS[sort], + "dir": dir, + "tags": get_tags_filter(tags), + } of String => String + + # count number of matching rows + num_rows = (one(:count_posts, sql_args, tmpl_args).to_s || 0).to_i + + # TODO: add Post class? + rows = [] of Hash(String, ::SQLite3::Value) + if num_rows > 0 + all(:get_posts, sql_args.merge({ + ":offset": ((page - 1) * limit).to_s, + ":limit": limit.to_s, + }), tmpl_args) do |row| + rows << row + nil + end + end + + # return result + Results(Hash(String, ::SQLite3::Value)).new( + page: page, + limit: limit, + num_rows: num_rows, + rows: rows, + ) + end + + private def get_tags_filter(tag_sets : Array(Array(String))) + (tag_sets.size > 0) ? template(:tags, { + "sql": tag_sets.map { |tags| + template(:tags, { + "sql": tags.map { |tag| + template(:tag, { + "tag": @models.db.quote(tag), + }) + }.join(" INTERSECTS "), + }) + }.join(" UNION "), + }) : " + SELECT post_id + FROM posts + LIMIT 0 + " end def add_post(req) |