How to achieve self-join in a table without foreign keys?

[qinrhx]

Your Question

I have a table below without foreign keys. I would like to perform a self-join query to retrieve the data of the current node and its parent node. The structure is defined as follows:

type ObjectX struct {
	Id       int64    `gorm:"Id"`                  // id
	ParentId int64    `gorm:"Parent_Id"`           // parent node id
	Name     string   `gorm:"Name"`                // name
	Parent   *ObjectX `gorm:"foreignKey:ParentId;references:Id"` // query the parent node
}

The query statement is:

var records []*ObjectX
err = db.Model(&ObjectX{}).Preload(clause.Associations).Scan(&records).Error

The result shows an error: "invalid field found for struct main.ObjectX's field Parent: define a valid foreign key for relations or implement the Valuer/Scanner interface."

Expected answer

How to achieve self-join in a table without foreign keys?