reprogl
267 строк · 5.9 Кб
1package repositories
2
3import (
4"database/sql"
5"errors"
6
7"xelbot.com/reprogl/models"
8)
9
10type CommentRepository struct {
11DB *sql.DB
12}
13
14func (cr *CommentRepository) GetLastUpdate(articleId int) (string, error) {
15var last string
16query := `
17SELECT
18DATE_FORMAT(src.last_update, '%y%j%H%i%s') AS last
19FROM (
20SELECT
21COALESCE(MAX(last_update), '1981-07-18 00:00:00') AS last_update
22FROM comments
23WHERE (post_id = ?)) AS src`
24
25err := cr.DB.QueryRow(query, articleId).Scan(&last)
26
27return last, err
28}
29
30func (cr *CommentRepository) GetCollectionByArticleId(articleId int) (models.CommentList, error) {
31query := `
32SELECT
33c.id,
34COALESCE(t.name, u.display_name, u.username) AS username,
35COALESCE(t.mail, u.mail) AS email,
36t.website,
37COALESCE(t.gender, u.gender) AS gender,
38c.commentator_id,
39c.user_id,
40c.text,
41c.tree_depth,
42c.time_created,
43COALESCE(t.rotten_link, 0) AS rotten_link,
44COALESCE(u.avatar_variant, 0) AS avatar_variant,
45c.deleted
46FROM comments AS c
47LEFT JOIN commentators AS t ON c.commentator_id = t.id
48LEFT JOIN users AS u ON c.user_id = u.id
49INNER JOIN (
50SELECT
51c1.id
52FROM
53comments AS c1,
54comments AS c2
55WHERE
56c1.post_id = ?
57AND c2.post_id = ?
58AND c1.tree_left_key <= c2.tree_left_key
59AND c1.tree_right_key >= c2.tree_right_key
60AND c2.deleted = 0
61GROUP BY c1.id
62) AS cc ON c.id = cc.id
63ORDER BY c.tree_left_key`
64
65rows, err := cr.DB.Query(query, articleId, articleId)
66if err != nil {
67return nil, err
68}
69
70defer rows.Close()
71
72comments := models.CommentList{}
73
74for rows.Next() {
75comment := models.Comment{}
76err = rows.Scan(
77&comment.ID,
78&comment.Name,
79&comment.Email,
80&comment.Website,
81&comment.Gender,
82&comment.CommentatorID,
83&comment.AuthorID,
84&comment.Text,
85&comment.Depth,
86&comment.CreatedAt,
87&comment.RottenLink,
88&comment.AvatarVariant,
89&comment.Deleted)
90
91if err != nil {
92return nil, err
93}
94
95comments = append(comments, &comment)
96}
97
98return comments, nil
99}
100
101// GetCollectionWithExtraDataByArticleId TODO use query builder with GetCollectionByArticleId
102func (cr *CommentRepository) GetCollectionWithExtraDataByArticleId(articleId int) (models.CommentList, error) {
103query := `
104SELECT
105c.id,
106COALESCE(t.name, u.display_name, u.username) AS username,
107COALESCE(t.mail, u.mail) AS email,
108t.website,
109COALESCE(t.gender, u.gender) AS gender,
110c.commentator_id,
111c.user_id,
112c.text,
113c.tree_depth,
114c.time_created,
115c.ip_addr,
116COALESCE(gco.country_code, '-') AS country_code,
117ta.user_agent,
118COALESCE(t.rotten_link, 0) AS rotten_link,
119COALESCE(u.avatar_variant, 0) AS avatar_variant,
120c.deleted
121FROM comments AS c
122LEFT JOIN commentators AS t ON c.commentator_id = t.id
123LEFT JOIN users AS u ON c.user_id = u.id
124LEFT JOIN geo_location AS gl ON c.ip_long = gl.ip_long
125LEFT JOIN geo_location_city AS gci ON gl.city_id = gci.id
126LEFT JOIN geo_location_country AS gco ON gci.country_id = gco.id
127LEFT JOIN tracking_agent ta on c.user_agent_id = ta.id
128INNER JOIN (
129SELECT
130c1.id
131FROM
132comments AS c1,
133comments AS c2
134WHERE
135c1.post_id = ?
136AND c2.post_id = ?
137AND c1.tree_left_key <= c2.tree_left_key
138AND c1.tree_right_key >= c2.tree_right_key
139AND c2.deleted = 0
140GROUP BY c1.id
141) AS cc ON c.id = cc.id
142ORDER BY c.tree_left_key`
143
144rows, err := cr.DB.Query(query, articleId, articleId)
145if err != nil {
146return nil, err
147}
148
149defer rows.Close()
150
151comments := models.CommentList{}
152
153for rows.Next() {
154comment := models.Comment{}
155err = rows.Scan(
156&comment.ID,
157&comment.Name,
158&comment.Email,
159&comment.Website,
160&comment.Gender,
161&comment.CommentatorID,
162&comment.AuthorID,
163&comment.Text,
164&comment.Depth,
165&comment.CreatedAt,
166&comment.IP,
167&comment.CountryCode,
168&comment.UserAgent,
169&comment.RottenLink,
170&comment.AvatarVariant,
171&comment.Deleted)
172
173if err != nil {
174return nil, err
175}
176
177comments = append(comments, &comment)
178}
179
180return comments, nil
181}
182
183func (cr *CommentRepository) GetMostActiveCommentators() (*models.CommentatorList, error) {
184query := `
185SELECT
186src.cnt,
187COALESCE(t.name, u.display_name, u.username) AS username,
188COALESCE(t.mail, u.mail) AS email,
189t.website,
190COALESCE(t.gender, u.gender) AS gender,
191COALESCE(t.rotten_link, 0) AS rotten_link,
192COALESCE(u.avatar_variant, 0) AS avatar_variant,
193src.commentator_id,
194src.user_id
195FROM (
196SELECT commentator_id,
197c.user_id,
198COUNT(c.id) AS cnt,
199MAX(c.time_created) AS last_time
200FROM comments AS c
201WHERE
202NOT (c.deleted = 1 AND c.tree_right_key - c.tree_left_key = 1)
203AND (c.user_id IS NULL OR c.user_id <> 1)
204GROUP BY commentator_id, user_id) AS src
205LEFT JOIN commentators AS t ON src.commentator_id = t.id
206LEFT JOIN users AS u ON src.user_id = u.id
207ORDER BY src.cnt DESC, src.last_time DESC
208LIMIT 8`
209
210rows, err := cr.DB.Query(query)
211if err != nil {
212return nil, err
213}
214
215defer rows.Close()
216
217commentators := models.CommentatorList{}
218
219for rows.Next() {
220commentator := &models.Commentator{}
221err = rows.Scan(
222&commentator.CommentsCount,
223&commentator.Name,
224&commentator.Email,
225&commentator.Website,
226&commentator.Gender,
227&commentator.RottenLink,
228&commentator.AvatarVariant,
229&commentator.CommentatorID,
230&commentator.AuthorID)
231
232if err != nil {
233return nil, err
234}
235
236commentators = append(commentators, commentator)
237}
238
239return &commentators, nil
240}
241
242func (cr *CommentRepository) FindForGravatar(id int) (*models.CommentatorForGravatar, error) {
243query := `
244SELECT
245c.id,
246c.mail,
247c.fake_email
248FROM commentators AS c
249WHERE (c.id = ?)`
250
251commentator := models.CommentatorForGravatar{}
252
253err := cr.DB.QueryRow(query, id).Scan(
254&commentator.ID,
255&commentator.Email,
256&commentator.FakeEmail)
257
258if err != nil {
259if errors.Is(err, sql.ErrNoRows) {
260return nil, models.RecordNotFound
261} else {
262return nil, err
263}
264}
265
266return &commentator, nil
267}
268