GORM - Has Many

w00j00ng351·2023년 1월 9일

gorm hasMany

GORM

목록 보기

1/1

참고자료: https://gorm.io/docs/has_many.html

내용

gorm 을 활용하여 1:N 관계의 테이블에 대한 쿼리를 수행

테스트용 데이터 준비

테스트 데이터 내용

users, user_details 테이블 생성
users 와user_details 테이블은 1:N 관계
user_details 테이블의 user_id 컬럼은 users 테이블의 id 컬럼에 대한 외래키
참고: 데이터베이스는 postgresql 을 사용함

테이블 생성 및 row 입력

DROP TABLE IF EXISTS user_details;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id serial4 NOT null,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  deleted_at timestamptz NULL,
  "name" varchar(255),
  CONSTRAINT users_pkey PRIMARY KEY(id)
);

CREATE TABLE user_details (
  id serial4,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  deleted_at timestamptz NULL,
  user_id int4 NOT NULL,
  "content" TEXT,
  CONSTRAINT user_details_pkey PRIMARY KEY(id),
  CONSTRAINT fk_user_details_user_id FOREIGN KEY(user_id) REFERENCES users(id)
);

INSERT INTO users ("name") VALUES ('Jane'), ('James'), ('Jessica'), ('Jeff');

INSERT INTO user_details (user_id, "content") VALUES (1, 'hello'), (1, 'world'), (2, 'good'), (2, 'day');

smallnest gen 으로 구조체 생성

참조: https://velog.io/@w00j00ng351/Go-smallnest-gen-%ED%99%9C%EC%9A%A9

smallnest gen 설치

$ go install github.com/smallnest/gen@latest

프로젝트 생성

디렉터리 생성

$ mkdir ~/mygorm

Go 모듈 초기화

$ cd ~/mygorm && go mod init mygorm

구조체 생성

gen 명령 수행

$ cd ~/mygorm && gen \
    --sqltype=postgres \
    --gorm \
    --connstr "user=postgres password=mypassword host=localhost port=15432 sslmode=disable TimeZone=Asia/Seoul" \
    --out=. \
    --host localhost \
    --port 15432 \
    --database public \
    --module=mygorm/model \
    --json \
    --gorm \
    --model=model \
    --overwrite \
    --guregu

Go 종속성 최신화

$ cd ~/mygorm && go mod tidy

GORM object 작성

디렉터리 생성

$ mkdir ~/mygorm/db

object 작성

$ cd ~/mygorm/db && vi db.go

package db

import (
	"log"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

var DB *gorm.DB

func init() {
	var err error
	DB, err = gorm.Open(
		postgres.Open(
			"user=postgres password=mypassword dbname=postgres host=localhost port=15432 sslmode=disable TimeZone=Asia/Seoul",
		),
	)
	if err != nil {
		log.Fatal(err.Error())
	}
}

종속성 최신화

$ cd ~/mygorm && go mod tidy

Has Many 활용 코드 작성

main.go 작성

$ cd ~/mygorm && vi main.go

package main

func main() {

}

종속성을 명시한 구조체 작성

model 패키지에 생성된 구조체에는 종속성 정보가 명시되지 않았음
model 패키지에 생성된 구조체를 임베딩하고, 종속성 정보를 명시하여 구조체를 생성
main.go 에 아래 코드 추가

type userDetailsT struct {
	model.UserDetails
}

type usersT struct {
	model.Users
	UserDetails []userDetailsT `gorm:"foreignKey:UserID;references:ID"`
}

쿼리 코드 작성

main.go 에 main 함수 작성

func main() {
	log.SetFlags(log.Lshortfile)

	users := []usersT{}
	if err := db.DB.Debug().
		Model(&usersT{}).
		Preload("UserDetails").
		Find(&users).
		Error; err != nil {
		log.Fatal(err.Error())
	}
	for _, u := range users {
		log.Printf("%#v\n", u)
	}
}

main.go 전체 코드

package main

import (
	"encoding/json"
	"log"
	"mygorm/db"
	"mygorm/model"
)

type userDetailsT struct {
	model.UserDetails
}

type usersT struct {
	model.Users
	UserDetails []userDetailsT `gorm:"foreignKey:UserID;references:ID"`
}

func main() {
	log.SetFlags(log.Lshortfile)

	users := []usersT{}
	if err := db.DB.Debug().
		Model(&usersT{}).
		Preload("UserDetails").
		Find(&users).
		Error; err != nil {
		log.Fatal(err.Error())
	}
	jsonBytes, err := json.MarshalIndent(users, "", "  ")
	if err != nil {
		log.Fatalln(err.Error())
	}
	log.Println(string(jsonBytes))
}

출력 결과

$ cd ~/mygorm && go run main.go

2023/01/09 13:39:10 /home/player/source/private/goproj/mygorm/main.go:26
[warn] Model mygorm/model.Users don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:39:10 /home/player/source/private/goproj/mygorm/main.go:26
[warn] Model main.usersT don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:39:10 
[warn] Model mygorm/model.UserDetails don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:39:10 /home/player/source/private/goproj/mygorm/main.go:26
[warn] Model main.userDetailsT don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:39:10 /home/player/source/private/goproj/mygorm/main.go:26
[0.702ms] [rows:4] SELECT * FROM "user_details" WHERE "user_details"."user_id" IN (1,2,3,4)

2023/01/09 13:39:10 /home/player/source/private/goproj/mygorm/main.go:26
[1.879ms] [rows:4] SELECT * FROM "users"
main.go:34: [
  {
    "id": 1,
    "created_at": "2023-01-09T10:01:48.113257+09:00",
    "updated_at": "2023-01-09T10:01:48.113257+09:00",
    "deleted_at": null,
    "name": "Jane",
    "UserDetails": [
      {
        "id": 1,
        "created_at": "2023-01-09T10:01:48.113257+09:00",
        "updated_at": "2023-01-09T10:01:48.113257+09:00",
        "deleted_at": null,
        "user_id": 1,
        "content": "hello"
      },
      {
        "id": 2,
        "created_at": "2023-01-09T10:01:48.113257+09:00",
        "updated_at": "2023-01-09T10:01:48.113257+09:00",
        "deleted_at": null,
        "user_id": 1,
        "content": "world"
      }
    ]
  },
  {
    "id": 2,
    "created_at": "2023-01-09T10:01:48.113257+09:00",
    "updated_at": "2023-01-09T10:01:48.113257+09:00",
    "deleted_at": null,
    "name": "James",
    "UserDetails": [
      {
        "id": 3,
        "created_at": "2023-01-09T10:01:48.113257+09:00",
        "updated_at": "2023-01-09T10:01:48.113257+09:00",
        "deleted_at": null,
        "user_id": 2,
        "content": "good"
      },
      {
        "id": 4,
        "created_at": "2023-01-09T10:01:48.113257+09:00",
        "updated_at": "2023-01-09T10:01:48.113257+09:00",
        "deleted_at": null,
        "user_id": 2,
        "content": "day"
      }
    ]
  },
  {
    "id": 3,
    "created_at": "2023-01-09T10:01:48.113257+09:00",
    "updated_at": "2023-01-09T10:01:48.113257+09:00",
    "deleted_at": null,
    "name": "Jessica",
    "UserDetails": []
  },
  {
    "id": 4,
    "created_at": "2023-01-09T10:01:48.113257+09:00",
    "updated_at": "2023-01-09T10:01:48.113257+09:00",
    "deleted_at": null,
    "name": "Jeff",
    "UserDetails": []
  }
]

더 복잡한 테이블 구조에 대한 연습

내용

상품을 검색했을 때 카테고리의 상세 정보까지 조회할 수 있도록 구현하고자 함
products, categories, product_categories 테이블이 있음
- products 테이블에서 상품 정보를 관리함
- categories 테이블에서 카테고리 정보를 관리함
- product_categories 테이블에서 상품의 카테고리 분류 정보를 저장
상세 데이터 내용
- 예시 카테고리 3개를 등록하고, 예시 상품 2개를 등록함
- 1번 상품을 1, 2번 카테고리에 등록
코드 내용
- 1번 상품을 조회했을 때 1, 2번 카테고리 정보가 조회되는지 확인

테스트 데이터 준비

아래 sql 수행

DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

CREATE TABLE products (
  id serial4 NOT null,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  deleted_at timestamptz NULL,
  "name" varchar(255),
  CONSTRAINT products_pkey PRIMARY KEY(id)
);

CREATE TABLE categories (
  id serial4 NOT null,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  deleted_at timestamptz NULL,
  "name" varchar(255),
  CONSTRAINT categories_pkey PRIMARY KEY(id)
);

CREATE TABLE product_categories (
  id serial4 NOT null,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  deleted_at timestamptz NULL,
  product_id int4 NOT NULL,
  category_id int4 NOT NULL,
  CONSTRAINT product_categories_pkey PRIMARY KEY (id),
  CONSTRAINT fk_product_categories_category FOREIGN KEY (category_id) REFERENCES public.categories(id) ON DELETE CASCADE,
  CONSTRAINT fk_product_categories_product FOREIGN KEY (product_id) REFERENCES public.products(id) ON DELETE CASCADE
);

INSERT INTO categories ("name") VALUES ('의류'), ('여성'), ('남성');

INSERT INTO products ("name") VALUES ('hat-abc'), ('pants-def'), ('shoes-gef');

INSERT INTO product_categories (product_id, category_id) VALUES (1, 1), (1, 2), (2, 2), (2, 3), (3, 3);

smallnest gen 으로 구조체 생성

$ cd ~/mygorm && gen \
    --sqltype=postgres \
    --gorm \
    --connstr "user=postgres password=mypassword host=localhost port=15432 sslmode=disable TimeZone=Asia/Seoul" \
    --out=. \
    --host localhost \
    --port 15432 \
    --database public \
    --module=mygorm/model \
    --json \
    --gorm \
    --model=model \
    --overwrite \
    --guregu

main.go 작성

package main

import (
	"encoding/json"
	"log"
	"mygorm/db"
	"mygorm/model"
)

type productT struct {
	model.Products
	ProductCategories []productCategoryT `gorm:"foreignKey:ProductID;references:ID"`
}

type categoryT struct {
	model.Categories
}

type productCategoryT struct {
	model.ProductCategories
	Categories categoryT `gorm:"foreignKey:ID;references:CategoryID"`
}

func main() {
	log.SetFlags(log.Lshortfile)

	products := []productT{}
	if err := db.DB.Debug().
		Model(&productT{}).
		Preload("ProductCategories", "deleted_at IS NULL").
		Preload("ProductCategories.Categories", "deleted_at IS NULL").
		Where("id = ?", 1).
		Find(&products).
		Error; err != nil {
		log.Fatal(err.Error())
	}
	jsonBytes, err := json.MarshalIndent(products, "", "  ")
	if err != nil {
		log.Fatal(err.Error())
	}
	log.Println(string(jsonBytes))
}

출력 결과

2023/01/09 13:40:10 /home/player/source/private/goproj/mygorm/main.go:33
[warn] Model mygorm/model.Products don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:40:10 /home/player/source/private/goproj/mygorm/main.go:33
[warn] Model main.productT don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:40:10 
[warn] Model mygorm/model.ProductCategories don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:40:10 /home/player/source/private/goproj/mygorm/main.go:33
[warn] Model main.productCategoryT don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:40:10 
[warn] Model mygorm/model.Categories don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:40:10 
[warn] Model main.categoryT don't match BeforeSaveInterface, should be `BeforeSave(*gorm.DB) error`. Please see https://gorm.io/docs/hooks.html

2023/01/09 13:40:10 /home/player/source/private/goproj/mygorm/main.go:33
[0.692ms] [rows:2] SELECT * FROM "categories" WHERE "categories"."id" IN (1,2) AND deleted_at IS NULL

2023/01/09 13:40:10 /home/player/source/private/goproj/mygorm/main.go:33
[1.370ms] [rows:2] SELECT * FROM "product_categories" WHERE "product_categories"."product_id" = 1 AND deleted_at IS NULL

2023/01/09 13:40:10 /home/player/source/private/goproj/mygorm/main.go:33
[2.887ms] [rows:1] SELECT * FROM "products" WHERE id = 1
main.go:41: [
  {
    "id": 1,
    "created_at": "2023-01-09T12:51:25.620151+09:00",
    "updated_at": "2023-01-09T12:51:25.620151+09:00",
    "deleted_at": null,
    "name": "hat-abc",
    "ProductCategories": [
      {
        "id": 1,
        "created_at": "2023-01-09T12:51:25.620151+09:00",
        "updated_at": "2023-01-09T12:51:25.620151+09:00",
        "deleted_at": null,
        "product_id": 1,
        "category_id": 1,
        "Categories": {
          "id": 1,
          "created_at": "2023-01-09T12:51:25.620151+09:00",
          "updated_at": "2023-01-09T12:51:25.620151+09:00",
          "deleted_at": null,
          "name": "의류"
        }
      },
      {
        "id": 2,
        "created_at": "2023-01-09T12:51:25.620151+09:00",
        "updated_at": "2023-01-09T12:51:25.620151+09:00",
        "deleted_at": null,
        "product_id": 1,
        "category_id": 2,
        "Categories": {
          "id": 2,
          "created_at": "2023-01-09T12:51:25.620151+09:00",
          "updated_at": "2023-01-09T12:51:25.620151+09:00",
          "deleted_at": null,
          "name": "여성"
        }
      }
    ]
  }
]

아래 코드는 위와 수행하는 내용이 같음
- 변경내용: productT 구조체 정의 변경

package main

import (
	"encoding/json"
	"log"
	"mygorm/db"
	"mygorm/model"
)

type productT struct {
	model.Products
	ProductCategories []struct {
		model.ProductCategories
		Categories struct {
			model.Categories
		} `gorm:"foreignKey:ID;references:CategoryID"`
	} `gorm:"foreignKey:ProductID;references:ID"`
}

func main() {
	log.SetFlags(log.Lshortfile)

	products := []productT{}
	if err := db.DB.Debug().
		Model(&productT{}).
		Preload("ProductCategories", "deleted_at IS NULL").
		Preload("ProductCategories.Categories", "deleted_at IS NULL").
		Where("id = ?", 1).
		Find(&products).
		Error; err != nil {
		log.Fatal(err.Error())
	}
	jsonBytes, err := json.MarshalIndent(products, "", "  ")
	if err != nil {
		log.Fatal(err.Error())
	}
	log.Println(string(jsonBytes))
}

w00j00ng351

시간이 만든 코드

GORM - Has Many

GORM

내용

테스트용 데이터 준비

테스트 데이터 내용

테이블 생성 및 row 입력

smallnest gen 으로 구조체 생성

smallnest gen 설치

프로젝트 생성

구조체 생성

GORM object 작성

Has Many 활용 코드 작성

main.go 작성

종속성을 명시한 구조체 작성

쿼리 코드 작성

main.go 전체 코드

출력 결과

더 복잡한 테이블 구조에 대한 연습

내용

테스트 데이터 준비

smallnest gen 으로 구조체 생성

main.go 작성

출력 결과

0개의 댓글