How to use a model with self-reference ?

[rodrigoodhin]

Hello everyone!

I've created a model with self-reference field but it's not working as expected.

My model:

type Category struct {
	db.Model
	Name        string    `json:"name" bun:",notnull,unique"`
	Description string    `json:"description"`
	Slug        string    `json:"slug" bun:",notnull"`
	RootID      uuid.UUID `json:"root_id" bun:",type:uuid,nullzero"`
	Root        *Category `json:"root" bun:",nullzero,rel:belongs-to,join:root_id=id"`
}

My Migration:

db.NewCreateTable().
		Model(&Category{}).
		IfNotExists().
		WithForeignKeys().
		ForeignKey(`(root_id) REFERENCES categories (id) ON UPDATE CASCADE ON DELETE SET NULL`).
		Exec(ctx)

My list query:

var categories []Category
db.NewSelect().Model(&Category{}).Scan(ctx, &categories)

How to return "Root" info in this query?
How to upsert this model to insert Category and Root?

Thank!

[l0nax]

You have two possibilities:

• Use JOIN
• Use RECURSIVE queries (Documentation for PostgreSQL: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE)

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.