Go使用GORM ORM套件做select join查詢的方式如下。
範例環境:
- Go 1.19
- GORM 2.0 (gorm.io/gorm v1.24.2)
- PostgreSQL 14
事前要求
參考「Golang GORM PostgreSQL基本設定」設定資料庫及安裝GORM套件。
建立EMPLOYEE
、DEPARTMENT
資料表及資料如下。
CREATE TABLE IF NOT EXISTS department (
id bigserial PRIMARY KEY,
name varchar(60) UNIQUE NOT NULL,
created_at timestamp NOT NULL
);
INSERT INTO public.department ("name", created_at)
VALUES('HR', '2022-12-29 22:37:02.199');
INSERT INTO public.department (id, "name", created_at)
VALUES('Marketing', '2022-12-29 22:37:23.538');
INSERT INTO public.department (id, "name", created_at)
VALUES('Logistic', '2022-12-29 22:39:14.389');
CREATE TABLE IF NOT EXISTS employee (
id bigserial PRIMARY KEY,
department_id bigint,
name varchar(60) UNIQUE NOT NULL,
age integer,
created_at timestamp NOT NULL
);
INSERT INTO public.employee (department_id, "name", age, created_at)
VALUES(1, 'John', 33, '2022-12-29 22:38:36.891');
INSERT INTO public.employee (id, department_id, "name", age, created_at)
VALUES(2, 'Mary', 28, '2022-12-29 22:38:36.893');
INSERT INTO public.employee (id, department_id, "name", age, created_at)
VALUES(NULL, 'Tony', 45, '2022-12-29 22:38:36.895');
DEPARTMENT
id | name | created_at
----+-----------+----------------------------
1 | HR | 2022-12-29 22:37:02.199582
2 | Marketing | 2022-12-29 22:37:23.538383
3 | Logistic | 2022-12-29 22:39:14.389925
EMPLOYEE
id | department_id | name | age | created_at
----+---------------+------+-----+----------------------------
1 | 1 | John | 33 | 2022-12-29 22:38:36.891199
2 | 2 | Mary | 28 | 2022-12-29 22:38:36.893386
3 | <null> | Tony | 45 | 2022-12-29 22:38:36.895071
範例
GORM使用gorm.DB.Select
搭配gorm.DB.Joins
做join查詢。
下面範例先用gorm.DB.Model
指定要查詢的model Employee
;
gorm.DB.Select
設定要查詢的欄位employee.id
, employee.department_id
, employee.name
, employee.age
, employee.created_at
;
gorm.DB.Joins
設定join條件,employee.department_id
參照department.id
;
gorm.DB.Scan
將查詢結果塞入model slice;。
main.go
package main
import (
"fmt"
"time"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"gorm.io/gorm/schema"
)
type Employee struct {
ID int64 // primary key, column name is `id`
DepartmentID int64 // column name is `department_id`
Name string // column name is `name`
Age int // column name is `age`
CreatedAt time.Time // column name is `created_at`
}
const (...)
func getGormDB() *gorm.DB {...}
func main() {
db := getGormDB()
emps := []Employee{}
db.Model(&Employee{}).
Select("employee.id, employee.department_id, employee.name, employee.age, employee.created_at").
Joins("inner join department on department.id = employee.department_id").
Scan(&emps)
/*
SELECT id, department_id, name, age, created_at
FROM employee
INNER JOIN department ON department_id = employee.department_id
*/
for _, emp := range emps {
fmt.Println(emp)
}
}
測試
執行印出以下。
{1 1 John 33 2022-12-29 22:38:36.891199 +0000 UTC}
{2 2 Mary 28 2022-12-29 22:38:36.893386 +0000 UTC}
GORM實際執行的SQL如下:
SELECT
employee.id,
employee.department_id,
employee.name,
employee.age,
employee.created_at
FROM "employee" INNER JOIN department ON department.id = employee.department_id
沒有留言:
張貼留言