GoのORM入門(go-pg ハンズオン)

## go-pgのチュートリアル

### 開発環境の説明
```markdown:開発環境
PC：Mac(CPUはintel製)
Go：1.17.6 ←Goのver大事。versionは1.16以降をインストールしてください。
開発エディタ：Visual Studio Code
go-pg:　v10.10.6 
```

```shell:ディレクトリ構成
~/go/src/go_pg_lesson $ tree -I ".data|schema"  
.
├── db
│   └── migrations
│       ├── 000001_create_users_table.down.sql
│       └── 000001_create_users_table.up.sql
├── docker-compose.yml
├── go.mod
├── go.sum
└── main.go

2 directories, 6 files
```

```yml:docker-compose.yml
version: '3.1'

services:
  postgres:
    container_name: postgres
    image: postgres:13
    ports:
      - 5432:5432
    volumes:
      - ./.data/postgres:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
```

## Lesson1: CRUD処理の基本操作
### 1. TBLの準備

```shell:TBL作成
CREATE TABLE IF NOT EXISTS users(
   id serial PRIMARY KEY,
   user_name VARCHAR (256) NOT NULL,
   password VARCHAR (256) NOT NULL,
   email VARCHAR (256) NOT NULL
);
```
※go-migrationでマイグレーション実施する場合は下記でいけます。気になる人は[記事](https://qiita.com/kouji0705/items/07e0a3d57a74b5af9817)を読んでみてね！！

### 2. アプリケーション作成
```go:アプリケーション作成
package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson1()
}

func Lesson1() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()
    user1 := &User{
        Name:   "admin",
		Password: "password",
        Email:  "admin1@admin",
    }
    _, err := db.Model(user1).Insert()
    if err != nil {
        panic(err)
    }

	users := &[]User{}

    err = db.Model(users).Select()
    if err != nil {
        panic(err)
    }

    fmt.Println(users)
}

```

### 3. アプリケーション実行
```go:アプリケーション実行（1回目）
~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin}]
```

```go:DB状態の確認
example=# select * from users;
 id | user_name | password |    email     
----+-----------+----------+--------------
  1 | admin     | password | admin1@admin
(1 row)
```

もう一回実行。
```go:アプリケーション実行（2回目）
~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin} {2 admin password admin1@admin}]
```
```go:アプリケーション実行（3回目）
~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin} {2 admin password admin1@admin} {3 admin password admin1@admin}]
```

これでいけますね！！！

## Lesson2: Where句の設定

### Lesson2-1. 単一Where句 

```go:ソース
package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_1()
}

func Lesson2_1() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new(User)
	err := db.Model(user).Where("id = ?", 1).Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
&{1 admin password admin1@admin}
```

### Lesson2-2. 複数Where句(OR文) 
```go:ソース
package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_2()
}

func Lesson2_2() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)
	err := db.Model(user).WhereOr("id = ?", 1).WhereOr("id = ?", 2).Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin} {2 admin password admin1@admin}]
```

### Lesson2-3. 複数Where句(AND文) 
```go:ソース
package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_3()
}

func Lesson2_3() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)
	err := db.Model(user).Where("id > ?", 1).
    Where("user_name = ?", "admin2").Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}
```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
&[{2 admin2 password admin2@admin}]
```

### Lesson2-4. 複数要素に含まれる場合 

```go:ソース
package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_4()
}

func Lesson2_4() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)

    ids := []int{1, 2}
	err := db.Model(user).Where("id in (?)", pg.In(ids)).
    Select()

	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}
```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
&[{1 admin1 password admin1@admin} {2 admin2 password admin2@admin}]
```

### Lesson2-5. あいまい検索
○検索条件：user_nameがadmin始まりのレコードを取得する。
○挿入データ
```sql:data
example=# select * from users;
 id | user_name | password |    email     
----+-----------+----------+--------------
  2 | admin2    | password | admin1@admin
  1 | admin1    | password | admin1@admin
  3 | kouji     | password | admin1@admin
(3 rows)
```

```go:ソース
package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_5()
}

func Lesson2_5() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)
	err := db.Model(user).Where("user_name LIKE ?", "admin%").Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}
```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
&[{2 admin2 password admin1@admin} {1 admin1 password admin1@admin}]
```

## Lesson3: 発展SQL(JOIN、GroupBy)

### Lesson3-1. 2つのTBLをJOINして結果を取得する。
### TBL変更

```sql:実行SQL
CREATE TABLE IF NOT EXISTS users(
   id serial PRIMARY KEY,
   name VARCHAR (256) NOT NULL,
   email VARCHAR (256) NOT NULL
);
CREATE TABLE IF NOT EXISTS stories(
   id serial PRIMARY KEY,
   title VARCHAR (256) NOT NULL,
   authorId INT NOT NULL
);
```

○挿入データ
```sql:data
example=# select * from users join stories on users.id = stories.authorId;
 id |   name    |        email        | id |   title    | authorid 
----+-----------+---------------------+----+------------+----------
  1 | kouji     | kouji@email.com     |  1 | ワンピース |        1
  2 | kkfactory | kkfactory@gmail.com |  2 | ヒロアカ   |        2
(2 rows)
```

```go:ソース
package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"name"`
    Email string `json:"email" pg:"email"`
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Email)
}

type Story struct {
    Id       int64 `json:"id" pg:"id"`
    Title    string `json:"title" pg:"title"`
    AuthorId int64 `json:"authorid" pg:"authorid"`
    Author   *User `json:"author" pg:"rel:has-one, fk:authorid"`
}

func (s Story) String() string {
    return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}


func main(){
	ExampleDB_Model()
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

    // Select all users.
    var users []User
    err := db.Model(&users).Select()
    if err != nil {
        panic(err)
    }

	var story []Story
    // story := new(Story)
    err = db.Model(&story).
        Relation("Author").
        Select()
    if err != nil {
        panic(err)
    }

    fmt.Println(users)
    fmt.Println(story)
}
```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go                                                                                                                                     
[User<1 kouji kouji@email.com> User<2 kkfactory kkfactory@gmail.com>]
[Story<1 ワンピース User<1 kouji kouji@email.com>> Story<2 ヒロアカ User<2 kkfactory kkfactory@gmail.com>>]
```

○学習のPOINT

```go:リレーションを貼る！！！
type Story struct {
    Id       int64 `json:"id" pg:"id"`
    Title    string `json:"title" pg:"title"`
    AuthorId int64 `json:"authorid" pg:"authorid"`
    Author   *User `json:"author" pg:"rel:has-one, fk:authorid"`
}

Author   *User `json:"author" pg:"rel:has-one, fk:authorid"`　これのようにリレーションを貼る必要があります。
今回は1対１のリレーションであるため、「rel:has-one」と定義。 fk:authoridを結合時のキーとなります。今回の場合だと、stories.authorid=users.idで連結しています！！
```

### Lesson3-2. 2つのTBLをJOINして結果を取得する。

```sql:実行SQL
CREATE TABLE IF NOT EXISTS users(
   id serial PRIMARY KEY,
   user_name VARCHAR (256) NOT NULL,
   password VARCHAR (256) NOT NULL,
   email VARCHAR (256) NOT NULL,
   department_no INT NOT NULL
);
CREATE TABLE IF NOT EXISTS department(
   id serial PRIMARY KEY,
   department_name VARCHAR (256) NOT NULL
);
ALTER TABLE "users" ADD FOREIGN KEY ("department_no") REFERENCES "department" ("id") ON DELETE SET NULL ON UPDATE NO ACTION;
```
### 作成後のER図
![スクリーンショット 2022-04-26 10.58.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/599049/d531f844-7609-e250-e0c2-269e6a92cfdc.png)

○挿入データ
```sql:data
example=# select * from users join department on users.department_no = department.id;
 id | user_name |  password  |   email   | department_no | id | department_name 
----+-----------+------------+-----------+---------------+----+-----------------
  1 | kouji     | password01 | gmail.com |             1 |  1 | システム開発部
  2 | kkfactory | password01 | gmail.com |             2 |  2 | システム運用部
(2 rows)
```

```go:ソース
package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
	tableName struct{} `pg:"users"`
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
    Password   string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
	DepartmentNo int64 `json:"department_no" pg:"department_no"`
	Department *Department `json:"department" pg:"rel:has-one, fk:department_no"`
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %s %d %v>", u.Id, u.Name, u.Email, u.DepartmentNo, u.Department)
}

type Department struct {
	tableName struct{} `pg:"department"`
    Id       int64 `json:"id" pg:"id"`
    DepartmentName   string `json:"department_name" pg:"department_name"`
}

func (s Department) String() string {
    return fmt.Sprintf("Department<%d %s>", s.Id, s.DepartmentName)
}


func main(){
	ExampleDB_Model()
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

    var users []User
    err := db.Model(&users).Select()
    if err != nil {
        panic(err)
    }
	fmt.Println(users)

	err = db.Model(&users).Relation("Department").Select()
    if err != nil {
        panic(err)
    }
	fmt.Println(users)
}

```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
[User<1 kouji gmail.com 1 <nil>> User<2 kkfactory gmail.com 2 <nil>>]
[User<1 kouji gmail.com 1 Department<1 システム開発部>> User<2 kkfactory gmail.com 2 Department<2 システム運用部>>]
```

### Lesson3-3. Group化する

○挿入データ
```sql:data
example=# select department_no, count(*) from users group by department_no;
 department_no | count 
---------------+-------
             1 |     1
             2 |     2
(2 rows)
```

```go:ソース
package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
	tableName struct{} `pg:"users"`
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
    Password   string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
	DepartmentNo int64 `json:"department_no" pg:"department_no"`
	Department *Department `json:"department" pg:"rel:has-one, fk:department_no"`
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %s %d %v>", u.Id, u.Name, u.Email, u.DepartmentNo, u.Department)
}

type Department struct {
	tableName struct{} `pg:"department"`
    Id       int64 `json:"id" pg:"id"`
    DepartmentName   string `json:"department_name" pg:"department_name"`
}

func (s Department) String() string {
    return fmt.Sprintf("Department<%d %s>", s.Id, s.DepartmentName)
}

func main(){
	ExampleDB_Model()
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

    var users []User
    err := db.Model(&users).Select()
    if err != nil {
        panic(err)
    }
	fmt.Println(users)

	var res []struct {
		DepartmentNo  int
		DepartmentCount int
	}

	err = db.Model(&users).Column("department_no").ColumnExpr("count(*) As department_count").Group("department_no").Select(&res)

    if err != nil {
        panic(err)
    }
	fmt.Println(res)
}
```

```shell:実行結果
~/go/src/go_pg_lesson $ go run main.go
[User<1 kouji gmail.com 1 <nil>> User<2 kkfactory gmail.com 2 <nil>> User<3 masahiro gmail.com 2 <nil>>]
[{1 1} {2 2}]
```

## Lesson4. サブクエリを実行する
SQLの中にSQLを書くサブクエリの例となります。

## Lesson4-1. サブクエリを実行する(From句をサブクエリで設定する)
```sql:DDL
CREATE TABLE IF NOT EXISTS books(
   id serial PRIMARY KEY,
   title VARCHAR (256) NOT NULL,
   author_id INT NOT NULL
);
```

```sql:実行したいSQL
SELECT * FROM (
  SELECT "book"."id", "book"."title", "book"."text"
  FROM "books" AS "book" WHERE (author_id = 1)
) AS book
```

```csv:格納データ
id	title	author_id
1	ワンピース	1
2	トリコ	1
3	呪術廻戦	1
4	ヒロアカ	2
```

```go:main.go
package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

func main() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
        Password: "postgres",
        Database: "sample",
        Addr: "localhost:5433",
    })
    defer db.Close()

    type Book struct {
        Id     int64 `json:"id" pg:"id"`
        Title   string `json:"title" pg:"title"`
        AuthorId string `json:"author_id" pg:"author_id"`
    }
    var books []Book

    // サブクエリを作成する。実際のSQLは「SELECT id FROM "books" AS "book" WHERE (author_id = 1)」
	authorBooks := db.Model((*Book)(nil)).Where("author_id = ?", 1)

	err := db.Model().TableExpr("(?) AS book", authorBooks).Select(&books)

    if err != nil {
        log.Fatalln(err)
    }
    fmt.Println("authorBooks: ",authorBooks)
    for _, v := range books {
        fmt.Println("v: ",v)
    }
    fmt.Println("books: ",books)
}
```

```go:実行結果
~/go/src/go-pg-sample $ go run main.go
authorBooks:  &{0xc00012a008 0xc00011ea80 <nil> 0xc000146090 0xc000146090 0 [] [] [] [0xc0001bdd40] [] map[] [] [0xc000135340] [] [] [] [] [] <nil> [] 0 0 <nil> <nil> []}
v:  {1 ワンピース 1}
v:  {2 トリコ 1}
v:  {3 呪術廻戦 1}
books:  [{1 ワンピース 1} {2 トリコ 1} {3 呪術廻戦 1}]
```
意外と簡単にできますね！！！go-pgのいいところですね！！

## Lesson５. bulk insertを実装する。

```go:main.go
package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson1()
}

func Lesson1() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	bulkinsert(db)
}

func bulkinsert(db *pg.DB) {
	
	var user1 = &User{
		Name: "admin1", Password: "password", Email: "admin1@admin",
	}
	var user2 = &User{
		Name: "admin2", Password: "password", Email: "admin1@admin",
	}
	var user3 = &User{
		Name: "admin3", Password: "password", Email: "admin1@admin",
	}

	var users []User

	users = append(users, *user1, *user2, *user3)

	res, err := db.Model(&users).Insert()

	if err != nil {
		panic(err)
	}
	fmt.Println(res, users)
}

```

```go:実行結果
~/go/src/go_pg_jikken$ go run main.go
&{0x1400013e3c0 3 3} [{3 admin1 password admin1@admin} {4 admin2 password admin1@admin} {5 admin3 password admin1@admin}]
~/go/src/go_pg_jikken$ go run main.go
&{0x140000e63c0 3 3} [{6 admin1 password admin1@admin} {7 admin2 password admin1@admin} {8 admin3 password admin1@admin}]
```