如何从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?

我有两个结构： p>

  type用户结构{\  n ID uint32 
第一个字符串
最后一个字符串
添加[] Address 
} 
 
type地址结构{
 ID uint32 
位置字符串
} 
  code>  pre> 
  
 我有两个表： p> 
 
 
 创建表用户（
 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）
）; 
  code>  pre> 
 
  “地址”    strong>表与“用户”  strong>表具有一对多 strong>关系。
因此，我如何通过内部联接 strong>从这两个表中获取数据？ 将其保存在“用户”结构实例中 strong> ？？  p> 
 
 
 注意 strong>：没有 gorm  str  ong>或其他 orm  strong>库？ p> 
  div>

答

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
}

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

相关推荐