reprogl

1
package repositories
2

3
import (
4
	"database/sql"
5
	"errors"
6

7
	"xelbot.com/reprogl/models"
8
)
9

10
type CategoryRepository struct {
11
	DB *sql.DB
12
}
13

14
func (cr *CategoryRepository) GetBySlug(slug string) (*models.Category, error) {
15
	query := `
16
		SELECT
17
			c.id,
18
			c.name,
19
			c.url,
20
			c.tree_left_key,
21
			c.tree_right_key,
22
			c.tree_depth
23
		FROM category AS c
24
		WHERE (c.url = ?)`
25

26
	category := &models.Category{}
27

28
	err := cr.DB.QueryRow(query, slug).Scan(
29
		&category.ID,
30
		&category.Name,
31
		&category.Slug,
32
		&category.LeftKey,
33
		&category.RightKey,
34
		&category.Depth)
35

36
	if err != nil {
37
		if errors.Is(err, sql.ErrNoRows) {
38
			return nil, models.RecordNotFound
39
		} else {
40
			return nil, err
41
		}
42
	}
43

44
	return category, nil
45
}
46

47
func (cr *CategoryRepository) GetCategoryTree() (*models.CategoryList, error) {
48
	query := `
49
		SELECT c.id,
50
			c.name,
51
			c.url,
52
			c.tree_depth
53
		FROM category AS c,
54
			(SELECT c0.id
55
				FROM category AS c0,
56
					category AS c1
57
				INNER JOIN posts AS p ON c1.id = p.category_id
58
				WHERE c0.tree_left_key <= c1.tree_left_key
59
					AND c0.tree_right_key >= c1.tree_right_key
60
					AND p.hide = 0
61
				GROUP BY c0.id) AS cnt
62
		WHERE c.id = cnt.id
63
		ORDER BY c.tree_left_key`
64

65
	rows, err := cr.DB.Query(query)
66
	if err != nil {
67
		return nil, err
68
	}
69

70
	defer rows.Close()
71

72
	categories := models.CategoryList{}
73

74
	for rows.Next() {
75
		category := &models.Category{}
76
		err = rows.Scan(
77
			&category.ID,
78
			&category.Name,
79
			&category.Slug,
80
			&category.Depth)
81

82
		if err != nil {
83
			return nil, err
84
		}
85

86
		categories = append(categories, category)
87
	}
88

89
	return &categories, nil
90
}
91