How to query data where column is not null in gorm

Question

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)

Answer 1

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