Do you know how to bind one-many association to Golang struct without orm?

Question

Do you know how to bind a one-to-many association to a Golang struct without using an ORM?

I want to bind the SQL results below to the following Golang struct:

CREATE TABLE
    users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255)
    );

CREATE TABLE
    posts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        title VARCHAR(255) NOT NULL
    );

INSERT INTO users (name) VALUES ("one"), ("two");

INSERT INTO
    posts (user_id, title)
VALUES (1, "one's post"), (1, "one's second post"), (1, "one's third post"), (2, "two's post"), (2, "two's second post"), (2, "two's third post");

SELECT u.id, u.name, p.id, p.title
FROM users AS u
JOIN posts AS p ON u.id = p.user_id;

type (
  User struct {
    ID int
    Name string
    Posts []*Post
  }

  Post struct {
    ID int
    Title string
  }
)

I often use MySQL. So I want the method with MySQL. But if you know good method with PostgreSQL, MariaDb and so on, please teach me that method.

Answer 1

func example() ([]*User, error) {
    // execute select query
    rows, err := db.Query(`SELECT ...`)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    // use a map for easy lookup of already scanned users
    userMap := make(map[int]*User)

    // iterate over the returned rows
    for rows.Next() {
        // declare a variable for each column returned by the query
        var (
            uid   int
            uname string
            // Use sql.NullXxx types since your JOIN
            // can potentially result in rows with NULLs,
            // or use COALESCE in your query, then you won't
            // have to use these types.
            pid    sql.NullInt64
            ptitle sql.NullString
        )

        // scan the current row's columns into the variables
        if err := rows.Scan(&uid, &uname, &pid, &ptitle); err != nil {
            return nil, err
        }

        // lookup the user in the map using the scanned id
        u, ok := userMap[uid]
        if !ok {
            // if not found, create a new instance of the user and store it in the map
            u = &User{ID: uid, Name: uname}
            userMap[uid] = u
        }

        // add the post instance to the user
        u.Posts = append(u.Posts, &Post{
            ID:    int(pid.Int64),
            Title: ptitle.String,
        })
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }

    // convert map into slice
    users := make([]*User, 0, len(userMap))
    for _, u := range userMap {
        users = append(users, u)
    }
    return users, nil
}

Answer 2

If you want to bind a one-to-many association to a Golang struct without an ORM, you can use the MySQL driver for Golang to execute a query that fetches the required data from the database and then manually map the result to the struct.

I will give you an example of how you can achieve this using the MySQL driver:

/* *  @Author: Sk Shahriar Ahmed Raka   * Email: [email protected]   * @Last Modified by:   Sk Shahirar Ahmed Raka  * @Last Modified time: 2023-04-23 00:16:31  */


package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type User struct {
    ID    int
    Name  string
    Posts []*Post
}

type Post struct {
    ID    int
    Title string
}

func main() {
    // Connect to the mysql database
    db, err := sql.Open("mysql", "username:password@tcp(localhost:3306)/databasename")
    if err != nil {
        fmt.Println("🚀 sql.Open() ~ err : ", err)

    }
    defer db.Close()
    // Prepare the SQL query in a string format
    query := `SELECT u.id, u.name, p.id, p.title FROM Users AS u JOIN posts AS p ON u.id = p.user_id `
    // Execute your  query string
    rows, err := db.Query(query)
    if err != nil {
        fmt.Println("🚀 db.Query(query)~ err : ", err)

    }
    defer rows.Close()

    // you have to map the results to the struct
    Users := []*User{}
    var userMap = make(map[int]*User)
    for rows.Next() {
        userID:=0
        userName:=""
        postID:=0
        postTitle:=""

        err := rows.Scan(&userID, &userName, &postID, &postTitle)
        if err != nil {
            fmt.Println("🚀 rows.Scan() ~ err : ", err)

        }

        user, ok := userMap[userID]
        if !ok {
            user = &User{ID: userID, Name: userName, Posts: make([]*Post, 0)}
            userMap[userID] = user
            Users = append(Users, user)
        }

        user.Posts = append(user.Posts, &Post{ID: postID, Title: postTitle})
    }
    // Print your results
    for _, user := range Users {
        fmt.Printf("UserID: %d, Name: %s\n", user.ID, user.Name)
        for _, post := range user.Posts {
            fmt.Printf("    PostID: %d, Title: %s\n", post.ID, post.Title)
        }
    }
}

For this example, first you need to connect to the MySQL database using the sql.Open function from the database/sql package and prepare the SQL query to fetch the required data. You then execute the query using the db.Query function and loop over the resulting rows to map the data to the User and Post structs manually.To map the results to the structs, you first create a map to store the User structs based on their ID, and then loop over the rows to create or update the User structs and their associated Post structs. you print the results by looping over the User structs and their associated Post structs.