如何将数据库记录加载到子结构中？

huangapple
go评论120阅读模式
英文:

How to load database records into sub structs?

问题

正在玩GO/Beego框架，并尝试查询数据库以将一些记录加载到结构体中。

以下是重要的代码：

  1. type User struct {
  2. 	UserId        int64     `orm:"pk"`
  3. 	FirstName     string    `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  4. 	LastName      string    `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  5. 	Email         string    `valid:"Required;MinSize(2);MaxSize(150);Email" required:"true" description:"user email address"`
  6. 	Password      string    `valid:"Required;MaxSize(60)" required:"true" description:"user plain text password" json:"-"`
  7. 	AccessLevel   uint64    `json:"-"`
  8. 	AuthKey       string    `json:"-"`
  9. 	Status        int       `json:"-"`
  10. 	DateAdded     time.Time `orm:"-" json:"-"`
  11. 	LastUpdated   time.Time `orm:"-" json:"-"`
  13. 	// relations
  14. 	Profile *UserProfile `orm:"rel(one)" json:"-"`
  15. }
  17. type UserProfile struct {
  18. 	UserId          int64  `orm:"pk"`
  19. 	Company         string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  20. 	VatNumber       string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  21. 	Website         string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  22. 	Phone           string `orm:"null" valid:"MinSize(2);MaxSize(150);Mobile"`
  23. 	Address1        string `orm:"null" valid:"MinSize(2);MaxSize(255)"`
  24. 	Address2        string `orm:"null" valid:"MinSize(2);MaxSize(255)"`
  25. 	City            string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  26. 	State           string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  27. 	Zip             string `orm:"null" valid:"MinSize(4);MaxSize(15)"`
  28. 	Country         string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  29. 	ConfirmationKey string `orm:"null" valid:"Length(40)" json:"-"`
  31. 	// relations
  32. 	User *User `orm:"reverse(one)" json:"-"`
  33. }
  35. func GetAllUsers() []User {
  36. 	o := orm.NewOrm()
  37. 	var users []User
  38. 	sql := `
  39. 	SELECT user.*, user_profile.* 
  40. 	   FROM user
  41. 	   INNER JOIN user_profile ON user_profile.user_id = user.user_id 
  42. 	WHERE 1`
  43. 	_, err := o.Raw(sql).QueryRows(&users)
  44. 	if err != nil {
  45. 		beego.Error(err)
  46. 	}
  47. 	beego.Info(users)
  48. 	return users
  49. }

上述代码的问题是，在调用GetAllUsers()时，嵌入在User中的结构体Profile不会被填充，所以我该如何使嵌入的结构体也被填充？

我还尝试使用以下方式获取用户：

  1. o.QueryTable("user").Filter("status", STATUS_ACTIVE).RelatedSel("Profile").All(&users)

它会生成一个类似于以下的SQL查询：

  1. SELECT T0.`user_id`, T0.`first_name`, T0.`last_name`, T0.`email`, T0.`password`, T0.`access_level`, T0.`auth_key`, T0.`status`, T0.`profile_id`, T1.`user_id`, T1.`company`, T1.`vat_number`, T1.`website`, T1.`phone`, T1.`address1`, T1.`address2`, T1.`city`, T1.`state`, T1.`zip`, T1.`country`, T1.`confirmation_key` FROM `user` T0 INNER JOIN `user_profile` T1 ON T1.`user_id` = T0.`profile_id` WHERE T0.`status` = ? LIMIT 1000

我不知道它是从哪里得到的在profile_id列上的连接，因为它甚至不存在，我也不确定如何指定正确的列进行连接，似乎它是从结构体名称中获取的。此外，我不喜欢无法指定要选择的内容。

非常感谢任何提示，我相信我错过了一些简单的东西。

谢谢。

英文:

Playing around with GO/Beego Framework and trying to query the database to load some records into a struct.

Bellow is the important code:

  1. type User struct {
  2. UserId int64 `orm:"pk"`
  3. FirstName string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  4. LastName string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  5. Email string `valid:"Required;MinSize(2);MaxSize(150);Email" required:"true" description:"user email address"`
  6. Password string `valid:"Required;MaxSize(60)"  required:"true" description:"user plain text password" json:"-"`
  7. AccessLevel uint64 `json:"-"`
  8. AuthKey string `json:"-"`
  9. Status int `json:"-"`
  10. DateAdded time.Time `orm:"-" json:"-"`
  11. LastUpdated time.Time `orm:"-" json:"-"`
  12. // relations
  13. Profile *UserProfile `orm:"rel(one)" json:"-"` // OneToOne relation
  14. }
  15. type UserProfile struct {
  16. UserId  int64 `orm:"pk"` // doesn't work without a PK, doesn't make sense, it's a fk...
  17. Company string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  18. VatNumber string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  19. Website string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  20. Phone string `orm:"null" valid:"MinSize(2);MaxSize(150);Mobile"`
  21. Address1 string `orm:"null" valid:"MinSize(2);MaxSize(255)"`
  22. Address2 string `orm:"null" valid:"MinSize(2);MaxSize(255)"`
  23. City string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  24. State string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  25. Zip string `orm:"null" valid:"MinSize(4);MaxSize(15)"`
  26. Country string `orm:"null" valid:"MinSize(2);MaxSize(150)"`
  27. ConfirmationKey string `orm:"null" valid:"Length(40)" json:"-"`
  28. // relations
  29. User *User `orm:"reverse(one)" json:"-"` // Reverse relationship (optional)
  30. }
  31. func GetAllUsers() []User {
  32. o := orm.NewOrm()
  33. var users []User
  34. sql := `
  35. SELECT user.*, user_profile.* 
  36. FROM user
  37. INNER JOIN user_profile ON user_profile.user_id = user.user_id 
  38. WHERE 1`
  39. _, err := o.Raw(sql).QueryRows(&users)
  40. if err != nil {
  41. beego.Error(err)
  42. }
  43. beego.Info(users)
  44. return users
  45. }

Now the problem with above is that upon calling GetAllUsers() the embed struct from User, that is Profile, doesn't get populated, so how would i go so that embed structs are also populated?

I have also tried to get the users with:

  1. o.QueryTable("user").Filter("status", STATUS_ACTIVE).RelatedSel("Profile").All(&users)

which produces a sql query like:

  1. SELECT T0.`user_id`, T0.`first_name`, T0.`last_name`, T0.`email`, T0.`password`, T0.`access_level`, T0.`auth_key`, T0.`status`, T0.`profile_id`, T1.`user_id`, T1.`company`, T1.`vat_number`, T1.`website`, T1.`phone`, T1.`address1`, T1.`address2`, T1.`city`, T1.`state`, T1.`zip`, T1.`country`, T1.`confirmation_key` FROM `user` T0 INNER JOIN `user_profile` T1 ON T1.`user_id` = T0.`profile_id` WHERE T0.`status` = ? LIMIT 1000

And i don't know from where it came up with the join on profile_id column since that doesn't even exists and i am not sure how to specify the right column to join, seems it takes it from the structure name. Also, i don't like the fact that there isn't a way to specify what to select.

Any hint is highly appreciated, i am sure it's something simple that i miss.

Thanks.

答案1

得分: 1

以下是翻译好的内容：

https://github.com/astaxie/beego/issues/384 有一个相关的讨论，但是是中文的。关键代码如下：

  1. type User struct {
  2.   Id   int
  3.   Name string
  4. }
  6. type Profile struct {
  7.   Id   int
  8.   Age  int
  9. }
  11. var users []*User
  12. var profiles []*Profile
  13. err := o.Raw(`SELECT id, name, p.id, p.age FROM user
  14.     LEFT OUTER JOIN profile AS p ON p.id = profile_id WHERE id = ?`, 1).QueryRows(&users, &profiles)
英文:

https://github.com/astaxie/beego/issues/384 has a relative talk, but it's Chinese.The key is below:

  1. type User struct {
  2. Id   int
  3. Name string
  4. }
  5. type Profile struct {
  6. Id   int
  7. Age  int
  8. }
  9. var users []*User
  10. var profiles []*Profile
  11. err := o.Raw(`SELECT id, name, p.id, p.age FROM user
  12. LEFT OUTER JOIN profile AS p ON p.id = profile_id WHERE id = ?`, 1).QueryRows(&users, &profiles)

huangapple
  • 本文由 发表于 2015年3月13日 23:03:00
  • 转载请务必保留本文链接：https://go.coder-hub.com/29035566.html
  • beego
  • go
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定