get Recursive field values in GORM

Question

I have a category table where has a recursive field named children. A parent may have child/children.

type Category struct {
    ID uint
    Name string
    ParentID *uint `gorm:"index" json:"parent_id"`
    Children []Category `gorm:"foreignkey:ParentID"`
}

when i use DB.Preload("Children").Find(&Category) i get only first children field, but that children have also many children more. how can I get all fields until children have no more children?

if you make a query for me that will be better for me understanding.

Thanks in advance

Answer 1

The query you are making is of the sort:

SELECT * FROM Category;
SELECT * FROM Children WHERE id IN (<your categories>);

this seems to be all good, except you don't have an entity Children, it is a field on the same Category table, if you just

var categories []Category;
DB.Find(&categories);

you should get all the categories from your table, from there you can filter things with Where() and such.

since you want all children from a category you could query with:

var categories []Category;
parentCategoryID := 1;
DB.Where("parentID = ?", parentCategoryID).Find(&categories);

this should yield all the categories that are children of a single parent.

You could go through each of those children and repeat the call to find all of their children and you could add them to a slice to flatten the data.

// continuing from previous code snippet
var flatCategories []Category;
for _, category := range categories {
    var tempCategories []Category;
    DB.Where("parentID = ?", category.ID).Find(&tempCategories);
    flatCategories = append(flatCategories, tempCategories...);
}

reference for querying with gorm

how preloading works with gorm