-
Notifications
You must be signed in to change notification settings - Fork 0
/
category.sql.go
266 lines (245 loc) · 6.04 KB
/
category.sql.go
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
// Code generated by sqlc. DO NOT EDIT.
// versions:
// sqlc v1.25.0
// source: category.sql
package category
import (
"context"
)
const createChildCategoryIfNotExists = `-- name: CreateChildCategoryIfNotExists :one
WITH inserted AS (
INSERT INTO categories
(category, slug, parent_id)
VALUES
($1, $2, $3)
ON CONFLICT (category, parent_id)
DO NOTHING
RETURNING id, category, slug, parent_id
)
SELECT id, category, slug, parent_id FROM inserted
UNION ALL
SELECT id, category, slug, parent_id FROM categories
WHERE category = $1 AND parent_id = $3
`
type CreateChildCategoryIfNotExistsRow struct {
ID int32
Category string
Slug string
ParentID *int32
}
func (q *Queries) CreateChildCategoryIfNotExists(ctx context.Context, category string, slug string, parentID *int32) (*CreateChildCategoryIfNotExistsRow, error) {
row := q.db.QueryRow(ctx, createChildCategoryIfNotExists, category, slug, parentID)
var i CreateChildCategoryIfNotExistsRow
err := row.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
)
return &i, err
}
const createTopCategory = `-- name: CreateTopCategory :one
INSERT INTO categories (
category, slug
) VALUES (
$1, $2
) RETURNING id, category, slug, parent_id
`
func (q *Queries) CreateTopCategory(ctx context.Context, category string, slug string) (*Category, error) {
row := q.db.QueryRow(ctx, createTopCategory, category, slug)
var i Category
err := row.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
)
return &i, err
}
const getCategory = `-- name: GetCategory :one
SELECT
c.id,
c.category,
c.slug,
c.parent_id,
CASE
WHEN EXISTS (SELECT 1 FROM categories WHERE parent_id = c.id) THEN false
ELSE true
END AS is_leaf_category
FROM
categories c
WHERE
c.id = $1
`
type GetCategoryRow struct {
ID int32
Category string
Slug string
ParentID *int32
IsLeafCategory bool
}
func (q *Queries) GetCategory(ctx context.Context, id int32) (*GetCategoryRow, error) {
row := q.db.QueryRow(ctx, getCategory, id)
var i GetCategoryRow
err := row.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
&i.IsLeafCategory,
)
return &i, err
}
const getCategoryByName = `-- name: GetCategoryByName :one
SELECT id, category, slug, parent_id FROM categories
WHERE category = $1
ORDER BY id
LIMIT 1
`
func (q *Queries) GetCategoryByName(ctx context.Context, category string) (*Category, error) {
row := q.db.QueryRow(ctx, getCategoryByName, category)
var i Category
err := row.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
)
return &i, err
}
const getCategoryWithAncestors = `-- name: GetCategoryWithAncestors :many
WITH RECURSIVE categories_cte AS (
SELECT c.id, c.category, c.slug, c.parent_id, 1 AS level
FROM categories c
WHERE c.id = $1
UNION
SELECT c.id, c.category, c.slug, c.parent_id, level + 1
FROM categories c
JOIN categories_cte cte ON cte.parent_id = c.id
)
SELECT id, category, slug, parent_id, level
FROM categories_cte
ORDER BY level DESC
`
type GetCategoryWithAncestorsRow struct {
ID int32
Category string
Slug string
ParentID *int32
Level int32
}
func (q *Queries) GetCategoryWithAncestors(ctx context.Context, id int32) ([]*GetCategoryWithAncestorsRow, error) {
rows, err := q.db.Query(ctx, getCategoryWithAncestors, id)
if err != nil {
return nil, err
}
defer rows.Close()
var items []*GetCategoryWithAncestorsRow
for rows.Next() {
var i GetCategoryWithAncestorsRow
if err := rows.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
&i.Level,
); err != nil {
return nil, err
}
items = append(items, &i)
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
const getCategoryWithSiblings = `-- name: GetCategoryWithSiblings :many
WITH category_cte AS (
SELECT parent_id FROM categories c WHERE c.id = $1
)
SELECT id, category, slug, parent_id FROM categories WHERE parent_id = (SELECT parent_id FROM category_cte)
`
func (q *Queries) GetCategoryWithSiblings(ctx context.Context, id int32) ([]*Category, error) {
rows, err := q.db.Query(ctx, getCategoryWithSiblings, id)
if err != nil {
return nil, err
}
defer rows.Close()
var items []*Category
for rows.Next() {
var i Category
if err := rows.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
); err != nil {
return nil, err
}
items = append(items, &i)
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
const getChildCategories = `-- name: GetChildCategories :many
SELECT id, category, slug, parent_id FROM categories where parent_id = $1
`
func (q *Queries) GetChildCategories(ctx context.Context, parentID *int32) ([]*Category, error) {
rows, err := q.db.Query(ctx, getChildCategories, parentID)
if err != nil {
return nil, err
}
defer rows.Close()
var items []*Category
for rows.Next() {
var i Category
if err := rows.Scan(
&i.ID,
&i.Category,
&i.Slug,
&i.ParentID,
); err != nil {
return nil, err
}
items = append(items, &i)
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
const getLeafCategoryIDs = `-- name: GetLeafCategoryIDs :many
WITH RECURSIVE categories_cte AS (
SELECT c.id, c.category, c.slug, c.parent_id
FROM categories c
WHERE c.id = $1
UNION ALL
SELECT c2.id, c2.category, c2.slug, c2.parent_id
FROM categories c2
JOIN categories_cte cte ON cte.id = c2.parent_id
)
SELECT array_agg(id)::INT[] AS leaf_category_ids
FROM categories_cte
WHERE id NOT IN (SELECT parent_id FROM categories WHERE parent_id IS NOT NULL)
`
func (q *Queries) GetLeafCategoryIDs(ctx context.Context, id int32) ([][]int32, error) {
rows, err := q.db.Query(ctx, getLeafCategoryIDs, id)
if err != nil {
return nil, err
}
defer rows.Close()
var items [][]int32
for rows.Next() {
var leaf_category_ids []int32
if err := rows.Scan(&leaf_category_ids); err != nil {
return nil, err
}
items = append(items, leaf_category_ids)
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}