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

Question

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?

Answer 1

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
}