Recursive self join

[CmpHDL]

Lets say we had the following

Generated model

package model

type Categories struct {
	ID               int64 
	Name             *string
	ParentCategoryID *int64
}

User defined model

type Category struct {
	model.Categories

	Subcategories []Category
}

Is it currently possible to-do a recursive select from a categories table into model with a property of type self (Category)?
To where:

----------ID, Name, Parent
Category: 1, one, null
Category: 2, two, 1
Category: 3, three, 2
Category: 4, four, 1

would have the struct of

[
  {
    "id": 1,
    "name": "one",
    "parent_id": null,
    "subcategories": [
      {
        "id": 2,
        "name": "two",
        "parent_id": 1,
        "subcategories": [
          {
            "id": 3,
            "name": "three",
            "parent_id": 2,
            "subcategories": []
          }
        ]
      },
      {
        "id": 4,
        "name": "four",
        "parent_id": 1,
        "subcategories": []
      }
    ]
  }
]

[go-jet]

Arbitrary depth self join is not possible. It is possible to scan if number of self join is predefined. One self join example - wiki.

Category type is circular dependency, so the scan would not fill Subcategories.
To fix it:

type Category struct {
	model.Categories

	Subcategories []struct {
            model.Categories  `alias:"subcategories.*"`
       }
}

Similarly 2 level deep categories:

type Category struct {
	model.Categories

	Subcategories1 []struct {
            model.Categories  `alias:"subcategories1.*"`

         	Subcategories2 []struct {
                    model.Categories  `alias:"subcategories2.*"`  
                }
       }
}

Note that each subcategory would need separate list of columns, meaning you'll need to self join n times the same table(with alias), and return n x table number of columns columns. The number of data returned from database might become huge.
For instance, if there is 10 categories, each of them with 10 subcategories categories, and each subcategory contains 10 more subcategories, resulting set would be: Rows(10 * 10 * 10) * Columns( 10 + 10 + 10) = Cells(30K).

[CmpHDL]

This is one of the solutions I came to as well, but its just too terrible to deal with code wise. Also the depth does have a limit, but not all branches go to that depth.

For anyone reading I scanned them into a flat slice then created a recursive function to create it into a tree based off the parent_id. (within go itself)

@go-jet What are you thoughts about creating a view within the database todo the recursive part then doing a SELECT from said view? How would we get that into models without having multiple levels of struct within the model definition, if even possible since we would probably we running into the same problem of cyclic type.

[go-jet]

At the moment, I don't see any other way, except to use recursive with statement, and then manually group the result. Query methods can't help with this kind of result set.
WITH RECURSIVE is not supported with the latest release, so I've pushed quick implementation on type-recursion branch, with a similar employee recursion test.

[CmpHDL]

Yeah I was battling different implementations this morning and also tried something similar with rows.Next, fastest implementation still seems to be a custom recursive function to build the tree, after optimizing that (thousands of rows) the speed more bearable. I'll mess with your WITH RECURSIVE, love your speed with ideas / features.