Hello Guys,
I work on an E-Commerce project. I need help with the category section for products.
may category table have these fields:
id - parent - title - level
and with this query in function parameter get parent UUID and return all children for this parent. but I don’t know how I must detect the child of the child and know the parent. in the hierarchy of children and parents to attach a product to that category. this is my query for return all children of a parent:
`
func GetChildrenCategories(db *sql.DB, w http.ResponseWriter, r *http.Request) {
ctx := context.Background()
type ParentID struct {
ParentUUID uuid.UUID `json:"parentuuid"`
}
parentID := ParentID{}
decoder := json.NewDecoder(r.Body)
if err := decoder.Decode(&parentID); err != nil {
utils.RespondError(w, http.StatusBadRequest, err.Error())
return
}
defer r.Body.Close()
query := `
WITH RECURSIVE subordinates AS (
SELECT
id,
parent,
title,
level
FROM
categories
WHERE
parent = $1
UNION
SELECT
e.id,
e.parent,
e.title,
e.level
FROM
categories e
INNER JOIN subordinates s ON s.id = e.parent
) SELECT
*
FROM
subordinates;
`
rows, err := db.QueryContext(ctx, query, parentID.ParentUUID)
if err != nil {
panic(err)
}
categories := make([]model.Category, 0)
for rows.Next() {
var category model.Category
if err := rows.Scan(&category.Id, &category.Parent, &category.Title, &category.Level); err != nil {
}
categories = append(categories, category)
}
utils.RespondJSON(w, http.StatusOK, categories)
}
`
please help me to fix this section