reprogl

1
package repositories
2

3
import (
4
	"database/sql"
5
	"errors"
6
	"fmt"
7
	"time"
8

9
	"github.com/doug-martin/goqu/v9"
10
	"xelbot.com/reprogl/models"
11
)
12

13
const RecentPostsCount = 6
14

15
type ArticleRepository struct {
16
	DB *sql.DB
17
}
18

19
func (ar *ArticleRepository) GetBySlug(slug string, isAdmin bool) (*models.Article, error) {
20
	ds := goqu.Dialect("mysql8").From(goqu.T("posts").As("p")).Select(
21
		"p.id",
22
		"p.title",
23
		"p.url",
24
		"p.text_post",
25
		"p.description",
26
		"p.time_created",
27
		"p.last_update",
28
		"p.comments_count",
29
		"p.views_count",
30
		"p.disable_comments",
31
		goqu.I("mf.path").As("image_path"),
32
		goqu.I("mf.width").As("image_width"),
33
		"mf.src_set",
34
		goqu.I("mf.description").As("image_alt"),
35
		goqu.I("c.name").As("cat_name"),
36
		goqu.I("c.url").As("cat_url"),
37
	).InnerJoin(
38
		goqu.T("category").As("c"),
39
		goqu.On(goqu.Ex{
40
			"c.id": goqu.I("p.category_id"),
41
		}),
42
	).LeftJoin(
43
		goqu.T("media_file").As("mf"),
44
		goqu.On(goqu.Ex{
45
			"mf.post_id":       goqu.I("p.id"),
46
			"mf.default_image": goqu.L("1"),
47
		}),
48
	).Where(
49
		goqu.Ex{
50
			"p.url": slug,
51
		},
52
	)
53

54
	if !isAdmin {
55
		ds = ds.Where(goqu.Ex{
56
			"p.hide": goqu.L("0"),
57
		})
58
	}
59

60
	query, params, _ := ds.Prepared(true).ToSQL()
61

62
	article := &models.Article{}
63

64
	err := ar.DB.QueryRow(query, params...).Scan(
65
		&article.ID,
66
		&article.Title,
67
		&article.Slug,
68
		&article.Text,
69
		&article.Description,
70
		&article.CreatedAt,
71
		&article.UpdatedAt,
72
		&article.CommentsCount,
73
		&article.Views,
74
		&article.DisabledComments,
75
		&article.ImagePath,
76
		&article.Width,
77
		&article.SrcSet,
78
		&article.Alt,
79
		&article.CategoryName,
80
		&article.CategorySlug)
81

82
	if err != nil {
83
		if errors.Is(err, sql.ErrNoRows) {
84
			return nil, models.RecordNotFound
85
		} else {
86
			return nil, err
87
		}
88
	}
89

90
	return article, nil
91
}
92

93
func (ar *ArticleRepository) GetIdBySlug(slug string) int {
94
	var id int
95

96
	err := ar.DB.QueryRow(
97
		`SELECT id FROM posts WHERE url = ?`,
98
		slug,
99
	).Scan(&id)
100

101
	if err != nil {
102
		return 0
103
	}
104

105
	return id
106
}
107

108
func (ar *ArticleRepository) GetCollection(page int, isAdmin bool) (*models.ArticlesPaginator, error) {
109
	countQuery := `
110
		SELECT
111
			COUNT(p.id) AS cnt
112
		FROM posts AS p
113
		WHERE p.hide = 0`
114

115
	ds := goqu.Dialect("mysql8").From(goqu.T("posts").As("p")).Select(
116
		"p.id",
117
		"p.title",
118
		"p.url",
119
		"p.text_post",
120
		"p.preview",
121
		"p.time_created",
122
		"p.comments_count",
123
		"p.hide",
124
		"mf.picture_tag",
125
		goqu.I("c.name").As("cat_name"),
126
		goqu.I("c.url").As("cat_url"),
127
	).InnerJoin(
128
		goqu.T("category").As("c"),
129
		goqu.On(goqu.Ex{
130
			"c.id": goqu.I("p.category_id"),
131
		}),
132
	).LeftJoin(
133
		goqu.T("media_file").As("mf"),
134
		goqu.On(goqu.Ex{
135
			"mf.post_id":       goqu.I("p.id"),
136
			"mf.default_image": goqu.L("1"),
137
		}),
138
	).Order(goqu.I("p.time_created").Desc())
139

140
	if !isAdmin {
141
		ds = ds.Where(goqu.Ex{
142
			"p.hide": goqu.L("0"),
143
		})
144
	}
145

146
	query, _, _ := ds.ToSQL()
147
	query += " LIMIT 10 OFFSET ?"
148

149
	params := make([]interface{}, 0)
150

151
	return ar.newPaginator(countQuery, query, page, params...)
152
}
153

154
func (ar *ArticleRepository) GetCollectionByCategory(category *models.Category, page int) (*models.ArticlesPaginator, error) {
155
	countQuery := `
156
		SELECT
157
			COUNT(p.id) AS cnt
158
		FROM posts AS p
159
		INNER JOIN category AS c ON c.id = p.category_id
160
		WHERE p.hide = 0
161
			AND c.tree_left_key >= ?
162
			AND c.tree_right_key <= ?`
163

164
	query := `
165
		SELECT
166
			p.id,
167
			p.title,
168
			p.url,
169
			p.text_post,
170
			p.preview,
171
			p.time_created,
172
			p.comments_count,
173
			p.hide,
174
			mf.picture_tag,
175
			c.name AS cat_name,
176
			c.url AS cat_url
177
		FROM posts AS p
178
		INNER JOIN category AS c ON c.id = p.category_id
179
		LEFT JOIN media_file mf ON (p.id = mf.post_id AND mf.default_image = 1)
180
		WHERE p.hide = 0
181
			AND c.tree_left_key >= ?
182
			AND c.tree_right_key <= ?
183
		ORDER BY time_created DESC
184
		LIMIT 10 OFFSET ?`
185

186
	params := make([]interface{}, 0)
187
	params = append(params, category.LeftKey, category.RightKey)
188

189
	return ar.newPaginator(countQuery, query, page, params...)
190
}
191

192
func (ar *ArticleRepository) GetCollectionByTag(tag *models.Tag, page int) (*models.ArticlesPaginator, error) {
193
	countQuery := `
194
		SELECT
195
			COUNT(p.id) AS cnt
196
		FROM posts AS p
197
		INNER JOIN relation_topictag AS at ON p.id = at.post_id
198
		WHERE p.hide = 0
199
			AND at.tag_id = ?`
200

201
	query := `
202
		SELECT
203
			p.id,
204
			p.title,
205
			p.url,
206
			p.text_post,
207
			p.preview,
208
			p.time_created,
209
			p.comments_count,
210
			p.hide,
211
			mf.picture_tag,
212
			c.name AS cat_name,
213
			c.url AS cat_url
214
		FROM posts AS p
215
		INNER JOIN category AS c ON c.id = p.category_id
216
		LEFT JOIN media_file mf ON (p.id = mf.post_id AND mf.default_image = 1)
217
		INNER JOIN relation_topictag AS at ON p.id = at.post_id
218
		WHERE p.hide = 0
219
			AND at.tag_id = ?
220
		ORDER BY time_created DESC
221
		LIMIT 10 OFFSET ?`
222

223
	params := make([]interface{}, 0)
224
	params = append(params, tag.ID)
225

226
	return ar.newPaginator(countQuery, query, page, params...)
227
}
228

229
func (ar *ArticleRepository) GetSitemapCollection() (models.SitemapItemList, error) {
230
	query := `
231
		SELECT
232
			url,
233
			updated_at
234
		FROM posts
235
		WHERE
236
			hide = 0
237
		ORDER BY time_created DESC
238
`
239

240
	rows, err := ar.DB.Query(query)
241
	if err != nil {
242
		return nil, err
243
	}
244

245
	defer rows.Close()
246

247
	articles := models.SitemapItemList{}
248

249
	for rows.Next() {
250
		item := models.SitemapItem{}
251
		err = rows.Scan(
252
			&item.Slug,
253
			&item.UpdatedAt)
254

255
		if err != nil {
256
			return nil, err
257
		}
258

259
		articles = append(articles, &item)
260
	}
261

262
	return articles, nil
263
}
264

265
func (ar *ArticleRepository) GetFeedCollection() (models.FeedItemList, error) {
266
	query := `
267
		SELECT
268
			p.id,
269
			p.title,
270
			p.url,
271
			p.text_post,
272
			mf.src_set,
273
			p.updated_at,
274
			p.time_created
275
		FROM posts AS p
276
		LEFT JOIN media_file AS mf ON (p.id = mf.post_id AND mf.default_image = 1)
277
		WHERE
278
			hide = 0
279
		ORDER BY time_created DESC
280
		LIMIT 25
281
`
282

283
	rows, err := ar.DB.Query(query)
284
	if err != nil {
285
		return nil, err
286
	}
287

288
	defer rows.Close()
289

290
	articles := models.FeedItemList{}
291

292
	for rows.Next() {
293
		item := models.FeedItem{}
294
		err = rows.Scan(
295
			&item.ID,
296
			&item.Title,
297
			&item.Slug,
298
			&item.Text,
299
			&item.SrcSet,
300
			&item.UpdatedAt,
301
			&item.CreatedAt)
302

303
		if err != nil {
304
			return nil, err
305
		}
306

307
		articles = append(articles, &item)
308
	}
309

310
	return articles, nil
311
}
312

313
func (ar *ArticleRepository) GetRecentPostsCollection(articleId int) (*models.RecentPostList, error) {
314
	query := fmt.Sprintf(`
315
		SELECT
316
			title,
317
			url
318
		FROM posts
319
		WHERE
320
			hide = 0
321
			AND id != ?
322
		ORDER BY time_created DESC
323
		LIMIT %d`, RecentPostsCount)
324

325
	rows, err := ar.DB.Query(query, articleId)
326
	if err != nil {
327
		return nil, err
328
	}
329

330
	defer rows.Close()
331

332
	articles := models.RecentPostList{}
333

334
	for rows.Next() {
335
		item := models.RecentPost{}
336
		err = rows.Scan(
337
			&item.Title,
338
			&item.Slug)
339

340
		if err != nil {
341
			return nil, err
342
		}
343

344
		articles = append(articles, &item)
345
	}
346

347
	return &articles, nil
348
}
349

350
func (ar *ArticleRepository) GetLastRecentPostsID() (int, error) {
351
	query := fmt.Sprintf(`
352
		SELECT
353
			MIN(src.id) AS id
354
		FROM (
355
			SELECT id
356
			FROM posts
357
			WHERE hide = 0
358
			ORDER BY time_created DESC
359
			LIMIT %d) AS src`, RecentPostsCount)
360

361
	var id int
362
	err := ar.DB.QueryRow(query).Scan(&id)
363
	if err != nil {
364
		return 0, err
365
	}
366

367
	return id, nil
368
}
369

370
func (ar *ArticleRepository) GetByIdForComment(id int) (*models.ArticleForComment, error) {
371
	query := `
372
		SELECT
373
			p.id,
374
			p.url,
375
			p.hide
376
		FROM posts AS p
377
		WHERE (p.id = ?)`
378

379
	article := &models.ArticleForComment{}
380

381
	err := ar.DB.QueryRow(query, id).Scan(
382
		&article.ID,
383
		&article.Slug,
384
		&article.Hidden)
385

386
	if err != nil {
387
		if errors.Is(err, sql.ErrNoRows) {
388
			return nil, models.RecordNotFound
389
		} else {
390
			return nil, err
391
		}
392
	}
393

394
	return article, nil
395
}
396

397
func (ar *ArticleRepository) newPaginator(countQuery, query string, page int, params ...interface{}) (*models.ArticlesPaginator, error) {
398
	var articleCount int
399

400
	err := ar.DB.QueryRow(countQuery, params...).Scan(&articleCount)
401
	if err != nil {
402
		return nil, err
403
	}
404

405
	pageCount := articleCount / 10
406
	if articleCount%10 != 0 {
407
		pageCount += 1
408
	}
409

410
	if page > pageCount {
411
		return nil, models.RecordNotFound
412
	}
413

414
	offset := 10 * (page - 1)
415
	params = append(params, offset)
416
	rows, err := ar.DB.Query(query, params...)
417
	if err != nil {
418
		return nil, err
419
	}
420

421
	defer rows.Close()
422

423
	articles, err := populateArticles(rows)
424
	if err != nil {
425
		return nil, err
426
	}
427

428
	return &models.ArticlesPaginator{Items: articles, CurrentPage: page, PageCount: pageCount}, nil
429
}
430

431
func (ar *ArticleRepository) GetMostVisitedArticlesOfMonth() ([]models.ArticleStatItem, error) {
432
	query := `
433
		SELECT
434
			p.title,
435
			p.url,
436
			COUNT(t.id) AS cnt
437
		FROM posts AS p
438
		INNER JOIN tracking AS t ON t.post_id = p.id
439
		INNER JOIN tracking_agent AS ta ON t.user_agent_id = ta.id
440
		WHERE t.time_created > ?
441
			AND ta.is_bot = 0
442
		GROUP BY p.id
443
		ORDER BY cnt DESC
444
		LIMIT 6`
445

446
	from := time.Now().Add(-30 * 24 * time.Hour).Format(time.DateTime)
447

448
	rows, err := ar.DB.Query(query, from)
449
	if err != nil {
450
		return nil, err
451
	}
452

453
	defer rows.Close()
454

455
	data := make([]models.ArticleStatItem, 0, 6)
456
	for rows.Next() {
457
		item := models.ArticleStatItem{}
458
		err = rows.Scan(
459
			&item.Title,
460
			&item.Slug,
461
			&item.Views)
462

463
		if err != nil {
464
			return nil, err
465
		}
466

467
		data = append(data, item)
468
	}
469

470
	return data, nil
471
}
472

473
func (ar *ArticleRepository) GetMostVisitedArticles() ([]models.ArticleStatItem, error) {
474
	query := `
475
		SELECT
476
			p.title,
477
			p.url,
478
			p.views_count AS cnt
479
		FROM posts AS p
480
		ORDER BY cnt DESC
481
		LIMIT 6`
482

483
	rows, err := ar.DB.Query(query)
484
	if err != nil {
485
		return nil, err
486
	}
487

488
	defer rows.Close()
489

490
	data := make([]models.ArticleStatItem, 0, 6)
491
	for rows.Next() {
492
		item := models.ArticleStatItem{}
493
		err = rows.Scan(
494
			&item.Title,
495
			&item.Slug,
496
			&item.Views)
497

498
		if err != nil {
499
			return nil, err
500
		}
501

502
		data = append(data, item)
503
	}
504

505
	return data, nil
506
}
507

508
func populateArticles(rows *sql.Rows) (models.ArticleList, error) {
509
	var err error
510

511
	articles := models.ArticleList{}
512

513
	for rows.Next() {
514
		article := &models.ArticleListItem{}
515
		err = rows.Scan(
516
			&article.ID,
517
			&article.Title,
518
			&article.Slug,
519
			&article.Text,
520
			&article.Preview,
521
			&article.CreatedAt,
522
			&article.CommentsCount,
523
			&article.Hidden,
524
			&article.PictureTag,
525
			&article.CategoryName,
526
			&article.CategorySlug)
527

528
		if err != nil {
529
			return nil, err
530
		}
531

532
		articles = append(articles, article)
533
	}
534

535
	return articles, nil
536
}
537