How to query data where column is not null in gorm

Issue

Here is my two models, I want to have my code return all the paths which has nodes inside them, and exclude all the path which has no nodes inside them

type Path struct {
    gorm.Model
    ID          uuid.UUID `json:"id" gorm:"type:uuid;primary_key"`
    Name        string    `json:"name" gorm:"type:varchar(255)"`
    Description string    `json:"description" gorm:"type:varchar(255)"`
    UserID      uuid.UUID `json:"user_id" gorm:"type:uuid"`
    Owner       User      `json:"owner" gorm:"foreignKey:UserID"`
    Nodes       []Node    `gorm:"foreignKey:PathID"`
}

type Node struct {
    gorm.Model
    ID          uuid.UUID `json:"_id" gorm:"type:uuid;primary_key"`
    Name        string    `json:"name" gorm:"type:varchar(255)"`
    Description string    `json:"description" gorm:"type:varchar(255)"`
    Url         string    `json:"url" gorm:"required"`
    Nodetype    string    `json:"nodetype" gorm:"type:varchar(255)"`
    PathID      uuid.UUID `json:"path_id" gorm:"type:uuid"`
    Path        Path      `gorm:"foreignKey:PathID"`
}

func (path *Path) BeforeCreate(tx *gorm.DB) (err error) {
    // UUID version 4
    path.ID = uuid.New()
    return
}

func (node *Node) BeforeCreate(tx *gorm.DB) (err error) {
    // UUID version 4
    node.ID = uuid.New()
    return
}

I want to fetch all the paths available with nodes

I have tried with this approach but not working

func GetAllPaths(c *fiber.Ctx) error {
    db := database.DB

    paths := []models.Path{}

    err := db.Debug().Joins("Owner").Preload("Nodes").Where("nodes IS NOT NULL").Find(&paths).Error
    if err != nil {
        return c.Status(500).SendString(err.Error())
    }

    var allPaths []serializer.PathResponseStruct

    for _, path := range paths {
        rpath := serializer.PathResponse(path)
        allPaths = append(allPaths, rpath)
    }

    return c.Status(200).JSON(allPaths)
}

The response I want is array paths with nodes, not empty array of nodes (null)

Solution

You could add an additional INNER JOIN to load only paths that have nodes. It would look something like this:

paths := []models.Path{}
err := db.Debug().Preload("Owner").Preload("Nodes").    //if you want to load the Path inside the node, then it should be .Preload("Nodes.Path")
          Joins("INNER JOIN nodes ON nodes.path_id = paths.id").Find(&paths).Error

Answered By – Emin Laletovic

Answer Checked By – Marilyn (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.