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

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?

Comment From: yiannisccmath

I think its because you renamed the parent id field.

Try one of the following

type ObjectX struct {
    Id       int64    `gorm:"Id"`
    ParentId int64   
    Name     string   `gorm:"Name"`
    Parent   *ObjectX `gorm:"foreignKey:ParentId"`
}

or

type ObjectX struct {
    Id       int64    `gorm:"Id"`
    ParentId int64    `gorm:"Parent_Id"` 
    Name     string   `gorm:"Name"`
    Parent   *ObjectX `gorm:"foreignKey:Parent_Id"`
}

Comment From: Dfriveraa

@yiannisccmath solution works !