Golang中如何使用Sqlx处理嵌套结构
Golang中如何使用Sqlx处理嵌套结构 你好，
type User Struct {
Id int64
Name string
Projects []Project // many to many
Emails []Email // one to many
Address Address // one to one
}
如何在 sqlx 中实现这种类型的结构。
3 回复
packs: 如何在 sqlx 中实现这种类型的结构。
我认为这是一个 SQL 问题，而不是 Go 问题，你可以考虑使用 JOIN 来连接 Projects、Emails 和 Address 表。
SQL JOIN
JOIN 操作通过将一个表中的列值与另一个表中的列值进行匹配，从而合并两个表中的记录。
JOIN Emails ON maintable_email=email_id
当使用“多对多”关系时，你可以在主表和项目表之间使用一个“链接表”。不过，通常一个项目只属于一个客户。
当你说“一对一”关系时，通常不需要额外的表。只需扩展主表即可…
更多关于Golang中如何使用Sqlx处理嵌套结构的实战系列教程也可以访问 https://www.itying.com/category-94-b0.html
我已经解决了这个问题。请给我反馈，这是否是 Go 语言中正确的编程方式。
type Installments_M struct {
Installment_ID int `db:"Installment_ID"`
Finyear int `db:"Finyear"`
College_code int `db:"College_code"`
Batch_code int `db:"Batch_code"`
Term_code int `db:"Term_code"`
Term_Name string `db:"Term_Name"`
Installment string `db:"Installment"`
Amount int `db:"Amount"`
}
type Installment_lineitem struct {
Installment_ID int `db:"Installment_ID"`
Lineitem int `db:"Lineitem"`
Finyear int `db:"Finyear"`
College_code int `db:"College_code"`
Batch_code int `db:"Batch_code"`
Term_code int `db:"Term_code"`
Term_Name string `db:"Term_Name"`
Fees_code int `db:"Fees_code"`
Fees_Name string `db:"Fees_Name"`
Installment string `db:"Installment"`
Amount int `db:"Amount"`
}
type Ins_MasterDetails struct {
Header Installments_M
Lineitem []Installment_lineitem
}
type Installments struct {
Installments []Ins_MasterDetails
}
func StudentFeesInstallments(_input req.CollFinBatchAadhaar) (resp.Installments, error, string) {
type onlyterm struct {
Term_code int `db:"Term_code"`
}
var _MasterDetails = resp.Ins_MasterDetails{}
var _Header = []resp.Installments_M{}
var _lineItem = []resp.Installment_lineitem{}
var _Installments = resp.Installments{}
_output := onlyterm{}
_Query := `SELECT Term_code FROM fees_student_installments
WHERE collegecode = ?
AND finyear = ?
AND batch_code = ?
AND aadhaar = ? limit 1 `
err := easyglobal.Mydb.Get(&_output, _Query,
_input.CollegeCode, _input.Finyear, _input.BatchCode, _input.Aadhaar)
if err != nil {
if err == sql.ErrNoRows {
return _Installments, nil, "Term Not Configured"
}
return _Installments, err, ""
}
if _output.Term_code > 0 {
_Query = `SELECT
M.Installment_ID,
M.Finyear,
M.College_code,
M.Batch_code,
M.Term_code,
FT.Term_Name,
M.Installment,
M.Amount
FROM fees_installment_h M,Fees_Terms FT
WHERE M.college_code = ?
AND M.finyear = ?
AND M.batch_code = ?
AND M.Term_code = FT.Term_code
and M.Term_code = ?
`
err := easyglobal.Mydb.Select(&_Header, _Query,
_input.CollegeCode, _input.Finyear, _input.BatchCode, _output.Term_code)
if err != nil {
if err == sql.ErrNoRows {
return _Installments, nil, "Record No found in Header Table "
}
return _Installments, err, ""
}
_Query = `SELECT
D.Installment_ID ,
D.Lineitem ,
D.Finyear ,
D.College_code ,
D.Batch_code ,
D.Term_code ,
D.Fees_code ,
FH.Fees_Name ,
D.Installment ,
D.Amount
FROM fees_installment_d D,fees_head FH
WHERE D.Installment_ID = ?
AND D.Fees_code = FH.Fees_code
`
for nCtr := 0; nCtr < len(_Header); nCtr++ {
err := easyglobal.Mydb.Select(&_lineItem, _Query, _Header[nCtr].Installment_ID)
if err != nil {
if err == sql.ErrNoRows {
return _Installments, nil, "Record No found in Header Table "
}
return _Installments, err, ""
}
_MasterDetails.Header = _Header[nCtr]
_MasterDetails.Lineitem = _lineItem
_Installments.Installments = append(_Installments.Installments, _MasterDetails)
}
}
return _Installments, nil, ""
}
在Golang中使用sqlx处理嵌套结构，可以通过以下方式实现：
1. 使用sqlx.Select配合JOIN查询
type User struct {
ID int64 `db:"id"`
Name string `db:"name"`
Projects []Project `db:"-"`
Emails []Email `db:"-"`
Address Address `db:"-"`
}
type Project struct {
ID int64 `db:"id"`
Name string `db:"name"`
}
type Email struct {
ID int64 `db:"id"`
Email string `db:"email"`
UserID int64 `db:"user_id"`
}
type Address struct {
ID int64 `db:"id"`
Street string `db:"street"`
City string `db:"city"`
UserID int64 `db:"user_id"`
}
// 查询用户及其关联数据
func GetUserWithRelations(db *sqlx.DB, userID int64) (*User, error) {
// 查询用户基本信息
user := &User{}
err := db.Get(user, "SELECT id, name FROM users WHERE id = $1", userID)
if err != nil {
return nil, err
}
// 查询项目（多对多）
err = db.Select(&user.Projects, `
SELECT p.id, p.name
FROM projects p
JOIN user_projects up ON p.id = up.project_id
WHERE up.user_id = $1
`, userID)
if err != nil {
return nil, err
}
// 查询邮箱（一对多）
err = db.Select(&user.Emails, `
SELECT id, email, user_id
FROM emails
WHERE user_id = $1
`, userID)
if err != nil {
return nil, err
}
// 查询地址（一对一）
err = db.Get(&user.Address, `
SELECT id, street, city, user_id
FROM addresses
WHERE user_id = $1
`, userID)
if err != nil && err != sql.ErrNoRows {
return nil, err
}
return user, nil
}
2. 使用StructScan处理复杂查询结果
// 使用单个查询获取所有数据
func GetUserWithAllData(db *sqlx.DB, userID int64) (*User, error) {
// 查询所有相关数据
rows, err := db.Queryx(`
SELECT
u.id as user_id,
u.name as user_name,
p.id as project_id,
p.name as project_name,
e.id as email_id,
e.email as email_address,
a.id as address_id,
a.street as street,
a.city as city
FROM users u
LEFT JOIN user_projects up ON u.id = up.user_id
LEFT JOIN projects p ON up.project_id = p.id
LEFT JOIN emails e ON u.id = e.user_id
LEFT JOIN addresses a ON u.id = a.user_id
WHERE u.id = $1
`, userID)
if err != nil {
return nil, err
}
defer rows.Close()
user := &User{}
projectsMap := make(map[int64]Project)
emailsMap := make(map[int64]Email)
addressSet := false
for rows.Next() {
var (
userID int64
userName string
projectID sql.NullInt64
projectName sql.NullString
emailID sql.NullInt64
emailAddress sql.NullString
addressID sql.NullInt64
street sql.NullString
city sql.NullString
)
err := rows.Scan(&userID, &userName, &projectID, &projectName,
&emailID, &emailAddress, &addressID, &street, &city)
if err != nil {
return nil, err
}
user.ID = userID
user.Name = userName
// 收集项目
if projectID.Valid {
project := Project{
ID: projectID.Int64,
Name: projectName.String,
}
projectsMap[projectID.Int64] = project
}
// 收集邮箱
if emailID.Valid {
email := Email{
ID: emailID.Int64,
Email: emailAddress.String,
}
emailsMap[emailID.Int64] = email
}
// 设置地址（一对一）
if addressID.Valid && !addressSet {
user.Address = Address{
ID: addressID.Int64,
Street: street.String,
City: city.String,
}
addressSet = true
}
}
// 转换map为slice
for _, project := range projectsMap {
user.Projects = append(user.Projects, project)
}
for _, email := range emailsMap {
user.Emails = append(user.Emails, email)
}
return user, nil
}
3. 使用sqlx.Named和事务处理插入
// 插入用户及其关联数据
func CreateUserWithRelations(db *sqlx.DB, user *User) error {
tx, err := db.Beginx()
if err != nil {
return err
}
defer func() {
if err != nil {
tx.Rollback()
}
}()
// 插入用户
query := `INSERT INTO users (name) VALUES (:name) RETURNING id`
rows, err := tx.NamedQuery(query, user)
if err != nil {
return err
}
if rows.Next() {
rows.Scan(&user.ID)
}
rows.Close()
// 插入地址（一对一）
if user.Address.Street != "" {
user.Address.UserID = user.ID
_, err = tx.NamedExec(`
INSERT INTO addresses (street, city, user_id)
VALUES (:street, :city, :user_id)
`, user.Address)
if err != nil {
return err
}
}
// 插入邮箱（一对多）
for i := range user.Emails {
user.Emails[i].UserID = user.ID
_, err = tx.NamedExec(`
INSERT INTO emails (email, user_id)
VALUES (:email, :user_id)
`, user.Emails[i])
if err != nil {
return err
}
}
// 插入项目关联（多对多）
for _, project := range user.Projects {
_, err = tx.Exec(`
INSERT INTO user_projects (user_id, project_id)
VALUES ($1, $2)
`, user.ID, project.ID)
if err != nil {
return err
}
}
return tx.Commit()
}
4. 使用自定义扫描器处理JSON字段
如果数据库支持JSON类型，可以这样处理：
type UserWithJSON struct {
ID int64 `db:"id"`
Name string `db:"name"`
Projects json.RawMessage `db:"projects_json"`
Emails json.RawMessage `db:"emails_json"`
Address json.RawMessage `db:"address_json"`
}
func GetUserWithJSON(db *sqlx.DB, userID int64) (*User, error) {
var userJSON UserWithJSON
err := db.Get(&userJSON, `
SELECT
u.id,
u.name,
COALESCE(
(SELECT json_agg(json_build_object('id', p.id, 'name', p.name))
FROM projects p
JOIN user_projects up ON p.id = up.project_id
WHERE up.user_id = u.id), '[]'
) as projects_json,
COALESCE(
(SELECT json_agg(json_build_object('id', e.id, 'email', e.email))
FROM emails e WHERE e.user_id = u.id), '[]'
) as emails_json,
(SELECT json_build_object('id', a.id, 'street', a.street, 'city', a.city)
FROM addresses a WHERE a.user_id = u.id) as address_json
FROM users u
WHERE u.id = $1
`, userID)
if err != nil {
return nil, err
}
user := &User{
ID: userJSON.ID,
Name: userJSON.Name,
}
// 解析JSON到结构体
json.Unmarshal(userJSON.Projects, &user.Projects)
json.Unmarshal(userJSON.Emails, &user.Emails)
json.Unmarshal(userJSON.Address, &user.Address)
return user, nil
}
这些方法展示了在sqlx中处理嵌套结构的不同方式，可以根据具体需求选择合适的方法。第一种方法使用多个查询，代码清晰但可能有性能开销；第二种方法使用单个查询，性能更好但代码复杂；第三种方法适合事务操作；第四种方法适合支持JSON的数据库。
