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
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 nameID 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
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
