diff options
author | Paul Duncan <pabs@pablotron.org> | 2016-03-09 01:13:00 -0500 |
---|---|---|
committer | Paul Duncan <pabs@pablotron.org> | 2016-03-09 01:13:00 -0500 |
commit | d73612df9875b57d9df1f64f757146e4accf7a30 (patch) | |
tree | c564c0d23af2cb81c3009f01c57138317613e1c2 /src | |
parent | 94529553ac724a11b6821dd1374c98b0f51b4f14 (diff) | |
download | old-guff-d73612df9875b57d9df1f64f757146e4accf7a30.tar.bz2 old-guff-d73612df9875b57d9df1f64f757146e4accf7a30.zip |
add get_tags
Diffstat (limited to 'src')
-rw-r--r-- | src/guff/models/tag.cr | 70 |
1 files changed, 66 insertions, 4 deletions
diff --git a/src/guff/models/tag.cr b/src/guff/models/tag.cr index 997cdfd..c828ebe 100644 --- a/src/guff/models/tag.cr +++ b/src/guff/models/tag.cr @@ -1,11 +1,38 @@ module Guff class TagModel < Model SQL = TemplateCache.new({ + get_tag_posts: " + SELECT a.post_id + + FROM post_tags a + JOIN tags b + ON (b.tag_id = a.tag_id) + + WHERE b.name = '%{tag}' + ", + + get_tags: " + SELECT a.tag_id, + a.name, + COUNT(*) AS num_posts + + FROM tags a + JOIN post_tags b + ON (b.tag_id = a.tag_id) + JOIN (SELECT DISTINCT post_id FROM (%{tags})) c + ON (c.post_id = b.post_id) + + WHERE %{filter} + + GROUP BY a.tag_id, a.name + ORDER BY num_posts, a.name DESC + ", + add_tags: " INSERT INTO tags(name) VALUES %{tags} ", - get_tags: " + get_ids: " SELECT tag_id, name @@ -19,7 +46,42 @@ module Guff super(models, SQL) end - def add_tags(tags : Array(String)) + def get_tags( + tags = [] of String : Array(String), + show_all = false : Bool, + ) : Array(Hash(String, String | Int32)) + r = [] of Hash(String, String | Int32) + + # build filter + if show_all + filter = "1 = 1" + else + filter = "a.name NOT LIKE '\\_%'" + end + + # exec query + all(:get_tags, nil, { + "filter": filter, + "tags": tags.map { |tag| + template(:get_tag_posts, { + "tag": @db.quote(tag), + }) + }.join(" INTERSECT "), + }) do |row| + r << { + "tag_id": row["tag_id"].to_s.to_i, + "name": row["name"]?.to_s, + "num_posts": row["num_posts"].to_s.to_i, + } + end + + # return results + r + end + + def add_tags( + tags = [] of String : Array(String) + ) missing_tags = get_missing_tags(tags) if missing_tags.size > 0 @@ -32,7 +94,7 @@ module Guff end private def get_missing_tags( - tags : Array(String) + tags = [] of String : Array(String) ) : Array(String) # get ids of existing tags ids = get_ids(tags) @@ -46,7 +108,7 @@ module Guff ) : Hash(String, Int32) r = {} of String => Int32 - all(:get_tags, nil, { + all(:get_ids, nil, { "tags": tags.map { |tag| "'" + @db.quote(tag) + "'" }.join(','), |