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

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.

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
}

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: skshahriarahmedraka@gmail.com   * @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.