Gorm returns just one instead of multiple results

Question

I wrote blow codes and it returns just 1 row instead of 4:

package main

import (
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

type Post struct {
    gorm.Model
    Title    string
    Text     string
    Comments []Comment
}

type Comment struct {
    gorm.Model
    Text   string
    PostID uint `gorm:"foreignkey:ID;association_foreignkey:PostID"`
}

func main() {
    db, err := gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic("failed to connect to database")
    }
    defer db.Close()

    db.DropTableIfExists(&Post{}, &Comment{})
    db.AutoMigrate(&Post{}, &Comment{})

    // fill db
    db.Create(&Post{Title: "test1 title", Text: "text1"})
    db.Create(&Post{Title: "test2 title", Text: "text2"})
    db.Create(&Post{Title: "test3 title", Text: "text3"})
    db.Create(&Comment{Text: "test1 comment1", PostID: 3})
    db.Create(&Comment{Text: "test2 comment1", PostID: 2})
    db.Create(&Comment{Text: "test3 comment2", PostID: 2})
    db.Create(&Comment{Text: "test4 comment3", PostID: 2})
    db.Create(&Comment{Text: "test5 comment4", PostID: 2})
    db.Create(&Comment{Text: "test6 comment1", PostID: 1})
    //end fill db

    var myPost Post
    var comments Comment
    db.First(&myPost, 2)
    db.Model(&myPost).Related(&comments)

    fmt.Println(myPost)
    fmt.Println(comments)
}

and this is my output:

{{2 2019-04-08 17:04:20.3781288 +0430 +0430 2019-04-08 17:04:20.3781288 +0430 +0430 <nil>} test2 title text2 []}
{{5 2019-04-08 17:04:20.4091133 +0430 +0430 2019-04-08 17:04:20.4091133 +0430 +0430 <nil>} test5 comment4 2}

you can see just one row:

test5 comment4 

and I expect this result:

test2 comment1
test3 comment2
test4 comment3
test5 comment4

What should I do to get 4 rows result?

I already read all the documentation of gorm. and this example of doc is not working for me as I expect http://doc.gorm.io/associations.html#has-many

Has Many
// User has many emails, UserID is the foreign key
type User struct {
    gorm.Model
    Emails   []Email
}

type Email struct {
    gorm.Model
    Email   string
    UserID  uint
}

db.Model(&user).Related(&emails)
//// SELECT * FROM emails WHERE user_id = 111; // 111 is user's primary key

Answer 1

I was thinking on this problem since yesterday and I found the problem just now. I just forgot to define var comment as an array.

"The one related to your question is that you are not passing a slice to gorm and expecting a slice in return. change var comments Comment to var comments []Comment. Notice the slice []" Abdullah said

var comments Comment

to

var comments []Comment

thanks to Abdullah

Answer 2

There quite a few issues in the attached snippet, will tackle them one by one:

#1

type Post struct {
    gorm.Model
    Title    string
    Text     string
    Comments []Comment
}

type Comment struct {
    gorm.Model
    Text   string
    PostID uint `gorm:"foreignkey:ID;association_foreignkey:PostID"`
}

Here, the assignments of the foreign key foreignkey:ID as well as the association foreign key are both unnecessary and misplaced.

For the Foreign Key: By default, gorm uses the owner’s type name plus the name of its primary key field. In your case: PostID.

• Post is the the owner’s type name
• ID is its primary key.

You would only need to use the forignkey tag if you want to change the name of the feild in the Comment struct. For example, PostNumber instead of PostID. So you would need to add a tag with foreignkey:PostNumber and change the PostID in Comment to PostNumber.

For the Association ForeignKey, It's used if you want to tell gorm to use a different member other than the owner's primary key. For example, AnotherID in the below example.

Another problem would be that you should specify these tags on the has many fields and not the foreign key itself. A complete example would look like this:

type Post struct {
    gorm.Model
    AnotherID uint     <-------------------------------------------------------
    Title     string                                                           |
    Text      string                                                           |
    Comments  []Comment `gorm:"foreignkey:PostNumber;association_foreignkey:AnotherID"`
}                                             |
                                              |
type Comment struct {                         |
    gorm.Model                                |
    Text       string                         |
    PostNumber uint    <----------------------
}

Note that these two have to have the same type.

---

#2

One can argue about the usage of defer db.Close(). From the docs,

It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines.

In this example, it's fine to defer the closing of the database. Though, it will happen automatically if you don't call it. The main reason why I am commenting about it is to tell you that in big applications, you don't need to do that with each connection. It's safe to just call sql.Open() on a global variable and use it without the need to db.Close().

In this case, you also don't want it to open as many connections as it pleases so you might want to fine tune the following parameters:

db.DB().SetConnMaxLifetime(X) // sets the maximum amount of time a connection may be reused.
db.DB().SetMaxIdleConns(X) // sets the maximum number of connections in the idle connection pool.
db.DB().SetMaxOpenConns(X) // sets the maximum number of open connections to the database.

See this discussion for more information.

---

#3

The below calls can fail:

db.DropTableIfExists(&Post{}, &Comment{})

db.AutoMigrate(&Post{}, &Comment{})

db.Create(&Post{Title: "test1 title", Text: "text1"})

So, ALWAYS check for errors, you can do that with checking the Error member of the gorm.DB struct:

err = db.DropTableIfExists(&Post{}, &Comment{}).Error
if err != nil {
    // handle error
}

err = db.AutoMigrate(&Post{}, &Comment{}).Error
// Check error

err = db.Create(&Post{Title: "test1 title", Text: "text1"}).Error
// Check error

---

#4

This is the answer to your question:

You are passing not passing a slice of Comment to db.Model(&myPost).Related(&comments) and expecting a slice in return which will not work for obvious reasons, so you would need to change:

var comments Comment

to

var comments []Comment