douying9296
2018-01-25 17:17
浏览 567

如何从MySQL中具有一对多关系的两个表中获取数据?

I have two struct:

type User struct {
    Id uint32
    First string
    Last string
    Adds []Address
}

type Address struct {
    Id uint32
    Location string
}

And i have two table:

create table user (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    First VARCHAR(40) NULL,
    Last VARCHAR(40) NULL,
    PRIMARY KEY (Id)
);

create table address (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    UserId INT UNSIGNED NOT NULL,
    Location VARCHAR(400) NOT NULL,
    FOREIGN KEY (UserId) REFERENCES user (Id),
    PRIMARY KEY (Id)
);

"address" table has one to many relationship with "user" table. so how can i fetch data from these two table with inner join and save it in "user" struct instance?

Note: Without gorm or other orm library?

图片转代码服务由CSDN问答提供 功能建议

我有两个结构:

  type用户结构{\  n ID uint32 
第一个字符串
最后一个字符串
添加[] Address 
} 
 
type地址结构{
 ID uint32 
位置字符串
} 
   
  
 

我有两个表:

 创建表用户(
 ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
 First VARCHAR(40)NULL,
 Last  VARCHAR(40)NULL,
主键(Id)
); 
 
创建表地址(
 ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
 UserId INT UNSIGNED NOT NULL,
位置VARCHAR(400)NOT NULL  ,
 FOREIGN KEY(UserId)参考用户(Id),
主键(Id)
); 
   
 
 

“地址” 表与“用户” 表具有一对多关系。 因此,我如何通过内部联接从这两个表中获取数据? 将其保存在“用户”结构实例中 ??

注意:没有 gorm 或其他 orm 库?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • doushang1778 2018-01-25 17:28
    最佳回答

    Using a single query and a JOIN:

    func GetUser(db *sql.DB, id int) (*User, error) {    
        rows, err := db.Query(`
            SELECT
                User.Id AS UserId,
                User.First AS UserFirst,
                User.Last AS UserLast,
                Location.Id AS LocationId,
                Location.Location AS LocationLocation
            FROM User
            LEFT JOIN Location ON
                User.Id = Location.UserId
            WHERE User.Id = ?
        `, id)
        if err != nil {
            return nil, err
        }
        defer rows.Close()
    
        var u *User
        for rows.Next() {
            if u == nil {
                u = new(User)
            }
            var locationID sql.NullInt64
            var location sql.NullString
            err := rows.Scan(
                &u.Id,
                &u.First,
                &u.Last,
                &locationID,
                &location,
            )
            if err != nil {
                return nil, err
            }
            if locationID.Valid && location.Valid {
                u.Adds = append(u.Adds, Address{
                    Id:       uint32(locationID.Int64),
                    Location: location.String,
                })
            }
        }
    
        if err := rows.Err(); err != nil {
            return nil, err
        }
    
        return u, nil
    }
    

    Alternatively, you can use two queries, which is cleaner in my opinion:

    func GetUser(db *sql.DB, id int) (*User, error) {
        u := new(User)
        err := db.QueryRow(`
            SELECT
                Id,
                First,
                Last
            FROM
                user
            WHERE Id = ?   
        `, id).Scan(
            &u.Id,
            &u.First,
            &u.Last,
        )
        if err != nil {
            if err == sql.ErrNoRows {
                return nil, nil
            }
            return nil, err
        }
    
        rows, err := db.Query(`
            SELECT
                Id,
                Location
            FROM
                addresses
            WHERE UserId = ?
        `, id)
        if err != nil {
            return nil, err
        }
        defer rows.Close()
        for rows.Next() {
            var address Address
            err := rows.Scan(
                &address.Id,
                &address.Location,
            )
            if err != nil {
                return nil, err
            }
            u.Adds = append(u.Adds, address)
        }
    
        if err := rows.Err(); err != nil {
            return nil, err
        }
    
        return u, nil
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题