1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
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_ids: "
SELECT tag_id,
name
FROM tags
WHERE name IN (%{tags})
",
})
def initialize(models : Models)
super(models, SQL)
end
def get_tags(
tags : Array(String) = [] of String,
show_all : Bool = false,
) : Array(Hash(String, String | Int32))
r = [] of Hash(String, String | Int32)
# build filter
if show_all
filter = "1 = 1"
else
# exclude internal tags
filter = "substr(a.name, 1, 1) <> '_'"
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 : Array(String) = [] of String,
)
missing_tags = get_missing_tags(tags)
if missing_tags.size > 0
query(:add_tags, nil, {
"tags": missing_tags.map { |tag|
"('" + @db.quote(tag) + "')"
}.join(','),
})
end
end
private def get_missing_tags(
tags : Array(String) = [] of String,
) : Array(String)
# get ids of existing tags
ids = get_ids(tags)
# return missing tags
tags.reject { |tag| ids[tag]? }
end
private def get_ids(
tags : Array(String) = [] of String,
) : Hash(String, Int32)
r = {} of String => Int32
all(:get_ids, nil, {
"tags": tags.map { |tag|
"'" + @db.quote(tag) + "'"
}.join(','),
}) do |row|
# add to results
r[row["name"] as String] = row["tag_id"].to_s.to_i
end
# return result
r
end
end
end
|
