package categories import ( "database/sql" "errors" "fmt" _ "github.com/lib/pq" "strings" ) type Category struct { Id int Name string Parent sql.NullInt64 Children []*Category } var CategoriesTree []*Category var CategoriesFlat map[int]*Category // Cavet: slight cheat. All parents must have Category_id < their children. func LoadCategories(db *sql.DB) { CategoriesTree = []*Category{} CategoriesFlat = make(map[int]*Category) rows, err := db.Query("select categories.id, categories.name, categories.parent_id from categories order by coalesce(parent_id, id), parent_id is not null") if err != nil { fmt.Println("DB Error loading Categories:", err) return } defer rows.Close() for rows.Next() { category := &Category{Children: []*Category{}} err = rows.Scan(&category.Id, &category.Name, &category.Parent) if err != nil { fmt.Println("Categories DB Error: ", err) } CategoriesFlat[category.Id] = category if category.Parent.Valid { pid := int(category.Parent.Int64) CategoriesFlat[pid].Children = append(CategoriesFlat[pid].Children, category) } else { CategoriesTree = append(CategoriesTree, category) } } } func Add(db *sql.DB, name string, parent int) error { var err error if parent < 0 { _, err = db.Exec("INSERT INTO categories (name) VALUES ($1)", name) } else { _, err = db.Exec("INSERT INTO categories (name, parent_id) VALUES ($1, $2)", name, parent) } if err != nil { fmt.Println("Categories DB Error Add(): ", err) } return err } // Get parent id // Set parent of children to parent // set category of news to parent // finallly delete func Delete(db *sql.DB, id int) error { rows, err := db.Query("SELECT parent_id FROM categories WHERE id=$1", id) if err != nil { fmt.Println("Categories DB Error loading category parent id: ", err) return err } if !rows.Next() { fmt.Println("Categories DB Error loading category parent id: no category") return errors.New("No category") } var parent_id sql.NullInt64 err = rows.Scan(&parent_id) _, err = db.Exec("UPDATE categories SET parent_id =$2 WHERE parent_id=$1", id, parent_id) if err != nil { fmt.Println("Categories DB error changing child parent: ", err) return err } _, err = db.Exec("UPDATE news SET category_id =$2 WHERE category_id=$1", id, parent_id) if err != nil { fmt.Println("Categories DB error changing category of news: ", err) return err } _, err = db.Exec("DELETE FROM categories WHERE id=$1", id) if err != nil { fmt.Println("Categories DB Error Delete(): ", err) } return err } func (category *Category) ChangeParent(db *sql.DB, parent *Category) error { var err error if parent == nil { _, err = db.Exec("UPDATE categories SET parent_id = NULL WHERE id = $1", category.Id) } else { _, err = db.Exec("UPDATE categories SET parent_id = $2 WHERE id = $1", category.Id, parent.Id) } if err != nil { fmt.Println("Categories DB Error: ", err) } return err } // get the number of parents a category has func (category *Category) Depth() int { depth := 0 current := category for current.Parent.Valid { current = CategoriesFlat[int(current.Parent.Int64)] depth++ } return depth } // Helper function for templates (add form select list) func (category *Category) ToString() string { return strings.Repeat("- ", category.Depth()) + category.Name }